import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import warnings
import time
warnings.filterwarnings('ignore')

#################################################################### CHI SQUARE TEST ###################################################################
from scipy.stats import chi2_contingency

################################################################# FEATURE SELECTION IMPORTS ############################################################
import scipy.stats as stats
from scipy.stats import chi2_contingency
from sklearn.feature_selection import RFE 
from sklearn.linear_model import LogisticRegression 
from sklearn.linear_model import LogisticRegressionCV
from sklearn.tree import DecisionTreeClassifier

################################################################# KFOLD TRAINING-TESTING ###############################################################
from sklearn.model_selection import StratifiedKFold

################################################################# TRAIN-TEST SPLIT #####################################################################
from sklearn.model_selection import train_test_split

################################################################# PREPROCESSING #################################################################
from sklearn.preprocessing import MinMaxScaler

################################################# MODELS #####################################################################
from sklearn.linear_model import LogisticRegression
from sklearn.neighbors import KNeighborsClassifier
from sklearn.tree import DecisionTreeClassifier
from sklearn.naive_bayes import GaussianNB
from sklearn.svm import SVC
from sklearn.ensemble import RandomForestClassifier, GradientBoostingClassifier, AdaBoostClassifier
from sklearn.neural_network import MLPClassifier

################################################# METRICS ####################################################################
from sklearn.metrics import f1_score, recall_score, precision_score, accuracy_score, precision_recall_curve,roc_curve

################################### MODEL SELECTION & OPTIMIZATION ###########################################################
from sklearn.model_selection import StratifiedKFold
from sklearn.model_selection import train_test_split
from sklearn.model_selection import train_test_split, GridSearchCV
from sklearn.model_selection import RandomizedSearchCV

################################### LOG TRANSFORMATION###########################################################################
from sklearn.preprocessing import FunctionTransformer
from sklearn.metrics import accuracy_score, classification_report, precision_score, recall_score, f1_score, confusion_matrix, ConfusionMatrixDisplay

################################### CLUSTERING ###########################################################################
from sklearn.cluster import KMeans
from sklearn.metrics import euclidean_distances
from sklearn.preprocessing import StandardScaler
from sklearn.decomposition import PCA

df_store = pd.read_excel(r'Data_store.xlsx')
df_store

df_labels = pd.read_excel(r'Data_labels.xlsx')
df_labels.head(18)

df = df_labels.merge(df_store, on = 'idstore')

df[df.duplicated()]

df.shape

(150054, 18)

df.columns

Index(['idstore', 'sku', 'brand', 'oldpvp', 'new_pvp (discount)', 'labelqty',
       'weight (g)', 'Payment_method', 'Margin (%)', 'Profit (€)',
       'perc_expiring_sku', 'expiring_date', 'labelling_date', 'sell_date',
       'sold', 'type', 'selling_square_ft', 'district'],
      dtype='object')

df.head(20)

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 150054 entries, 0 to 150053
Data columns (total 18 columns):
 #   Column              Non-Null Count   Dtype         
---  ------              --------------   -----         
 0   idstore             150054 non-null  int64         
 1   sku                 150054 non-null  int64         
 2   brand               150054 non-null  object        
 3   oldpvp              150032 non-null  object        
 4   new_pvp (discount)  150026 non-null  object        
 5   labelqty            150054 non-null  int64         
 6   weight (g)          150050 non-null  object        
 7   Payment_method      150054 non-null  object        
 8   Margin (%)          150054 non-null  int64         
 9   Profit (€)          150054 non-null  float64       
 10  perc_expiring_sku   149929 non-null  float64       
 11  expiring_date       150054 non-null  object        
 12  labelling_date      150054 non-null  datetime64[ns]
 13  sell_date           80511 non-null   datetime64[ns]
 14  sold                149940 non-null  float64       
 15  type                150054 non-null  object        
 16  selling_square_ft   139275 non-null  float64       
 17  district            150054 non-null  object        
dtypes: datetime64[ns](2), float64(4), int64(4), object(8)
memory usage: 20.6+ MB

df.describe()

df['sold'].value_counts()

sold
1.0    80453
0.0    69487
Name: count, dtype: int64

sales_count = df['sell_date'].value_counts().sum()
non_sales_count = df['sell_date'].isna().sum()
print(sales_count)
print(non_sales_count)

80511
69543

df.skew(numeric_only=True)

idstore                0.166375
sku                   -0.072151
labelqty               0.000000
Margin (%)            -0.374411
Profit (€)           341.717605
perc_expiring_sku      0.261184
sold                  -0.146666
selling_square_ft      1.091003
dtype: float64

df.kurt(numeric_only=True)

idstore                  -1.121767
sku                      -1.118777
labelqty                  0.000000
Margin (%)               -1.260135
Profit (€)           126938.236692
perc_expiring_sku        -0.022512
sold                     -1.978515
selling_square_ft         0.693269
dtype: float64

df.describe(include = ['O'])

df['brand'].value_counts().head(20)

brand
 marca 2     18249
 Marca 2     17008
marca  2      9077
marca   2     9068
marca2        9043
marca 2       9037
Marca2        8840
Marca 2       8672
Marca  2      8616
Marca   2     8553
 Marca 7      2739
 marca 7      2426
 Marca 15     2051
 marca 15     1983
Marca7        1447
Marca 7       1385
Marca  7      1333
Marca   7     1319
marca  7      1227
marca 7       1197
Name: count, dtype: int64

df['expiring_date'].value_counts().head(10)

expiring_date
28/10/2021    4741
29/10/2021    4737
22/10/2021    4543
15/10/2021    4481
22-10-2021    4440
21/10/2021    4380
23/10/2021    4196
29-10-2021    4156
07/10/2021    3877
15-10-2021    3831
Name: count, dtype: int64

df['Payment_method'].value_counts().head(20)

Payment_method
        69543
Card    40398
Cash    40113
Name: count, dtype: int64

df['type'].value_counts()

type
Medium    62590
Small     60599
Large     26219
0           646
Name: count, dtype: int64

df['district'].value_counts()

district
Lisboa              14888
Porto               12018
Coimbra             10694
Braga               10050
Guarda               9699
Viana do Castelo     8943
Aveiro               8650
Viseu                8421
Castelo Branco       7770
Beja                 7664
Bragança             7230
Setúbal              7207
Évora                6975
Portalegre           6857
Vila Real            6820
Faro                 6432
Santarém             5946
Leiria               3790
Name: count, dtype: int64

df['weight (g)'].head(50)

0     260
1     260
2     260
3     260
4     260
5     260
6     260
7     260
8     260
9     260
10    260
11    260
12    260
13    260
14    222
15    222
16    222
17    222
18    222
19    222
20    222
21    222
22    222
23    222
24    222
25    222
26    222
27    222
28    222
29    222
30    222
31    222
32    222
33    222
34    222
35    222
36    222
37    222
38    222
39    222
40    222
41    222
42    222
43    222
44    222
45    222
46    NaN
47    222
48    222
49    222
Name: weight (g), dtype: object

df['oldpvp'].tail(10)

150044    2,49
150045    2.49
150046    2.49
150047    2.49
150048    2.49
150049    2.49
150050    2.49
150051    2.49
150052    3.49
150053    3.49
Name: oldpvp, dtype: object

df['new_pvp (discount)'].tail(10)

150044     1,24 (50%)
150045    1.24 (0.50)
150046    1.24 (0.50)
150047    1.24 (0.50)
150048    1.24 (0.50)
150049    1.24 (0.50)
150050    1.24 (0.50)
150051    1.24 (0.50)
150052    2.44 (0.30)
150053    2.44 (0.30)
Name: new_pvp (discount), dtype: object

df.isna().sum()

idstore                   0
sku                       0
brand                     0
oldpvp                   22
new_pvp (discount)       28
labelqty                  0
weight (g)                4
Payment_method            0
Margin (%)                0
Profit (€)                0
perc_expiring_sku       125
expiring_date             0
labelling_date            0
sell_date             69543
sold                    114
type                      0
selling_square_ft     10779
district                  0
dtype: int64

df.loc[(df['sold'] != 1) & (df['sell_date'].notna()),'sold']=1

df.loc[(df['sold'].isna()) & (df['sell_date'].isna()),'sold']=0

df['sold'].value_counts()

sold
1.0    80511
0.0    69543
Name: count, dtype: int64

sales_count = df['sell_date'].value_counts().sum()
non_sales_count = df['sell_date'].isna().sum()
print(sales_count)
print(non_sales_count)

80511
69543

df['new_pvp'] = df['new_pvp (discount)'].astype(str).str.split(' ').str[0]
df['discount'] = df['new_pvp (discount)'].astype(str).str.split(' ').str[1]

df[['new_pvp (discount)','new_pvp','discount']].head(11)

df['discount'] = df['discount'].apply(lambda x: str(x).replace('(', '') if pd.notna(x) else x)
df['discount'] = df['discount'].apply(lambda x: str(x).replace(')', '') if pd.notna(x) else x)
df['discount'] = df['discount'].apply(lambda x: str(x).replace('%', '') if pd.notna(x) else x)
df['discount'] = df['discount'].apply(lambda x: str(x).replace(',', '.') if pd.notna(x) else x)

df['discount'] = df['discount'].astype('float')

df['discount'].tail(10)

150044    50.0
150045     0.5
150046     0.5
150047     0.5
150048     0.5
150049     0.5
150050     0.5
150051     0.5
150052     0.3
150053     0.3
Name: discount, dtype: float64

df.loc[df['discount'] > 1, 'discount'] = df.loc[df['discount'] > 1, 'discount'] / 100

df['discount'].describe()

count    150026.000000
mean          0.302269
std           0.036940
min           0.010000
25%           0.300000
50%           0.300000
75%           0.300000
max           0.990000
Name: discount, dtype: float64

df['new_pvp'] = df['new_pvp'].apply(lambda x: str(x).replace(',', '.') if pd.notna(x) else x)
df['new_pvp'] = df['new_pvp'].astype('float')

df.drop('new_pvp (discount)', axis=1, inplace=True)

df.loc[df['new_pvp'].isna() & df['Margin (%)'].notna() & df['Profit (€)'].notna(), 'new_pvp'] = df['Profit (€)'] / df['Margin (%)'] *100

df['oldpvp'] = df['oldpvp'].apply(lambda x: str(x).replace(',', '.') if pd.notna(x) else x)
df['oldpvp'] = df['oldpvp'].astype('float')

df.loc[df['oldpvp'].isna() & df['new_pvp'].notna() & df['discount'].notna(), 'oldpvp'] = df['new_pvp'] / (1 - df['discount'])

df.loc[df['discount'].isna() & df['oldpvp'].notna() & df['new_pvp'].notna(), 'discount'] = df['new_pvp'] / df['oldpvp']

df['weight (g)'].replace(r'^\s*$', np.nan, regex=True, inplace=True)

df[df['sku'] == 2]['weight (g)'].value_counts()

weight (g)
222.0    101
Name: count, dtype: int64

df[df['sku'] == 4]['weight (g)'].value_counts()

weight (g)
134.0    453
Name: count, dtype: int64

peso_sku_2 = df.loc[df['sku'] == 2, 'weight (g)'].iloc[0]
df.loc[(df['sku'] == 2) & (df['weight (g)'].isna()), 'weight (g)'] = peso_sku_2

peso_sku_4 = df.loc[df['sku'] == 4, 'weight (g)'].iloc[0]
df.loc[(df['sku'] == 4) & (df['weight (g)'].isna()), 'weight (g)'] = peso_sku_4

df['weight (g)'].isna().sum()

424

df = df.dropna(subset=['weight (g)'])

df.loc[df['expiring_date'] == df['labelling_date'], 'perc_expiring_sku'] = 0

df['perc_expiring_sku'].isna().sum()

80

df = df.dropna(subset=['perc_expiring_sku'])

df.loc[df['sold']== 0,'Payment_method'] = 'No_Payment'

df[df['selling_square_ft'].isna()].groupby('type').size()

type
0          646
Medium    2028
Small     8078
dtype: int64

df.loc[df['type'] == 0, 'type'] = 'Small'

if {"district", "selling_square_ft", "type"}.issubset(df.columns):
    
    plt.figure(figsize=(12, 6))
    sns.boxplot(x=df["district"], y=df["selling_square_ft"], hue=df["type"], palette="coolwarm")
    plt.xticks(rotation=90)
    plt.title("Distribution of Sales Area by District and Store Type")
    plt.xlabel("District")
    plt.ylabel("Sales Area (ft²)")
    plt.legend(title="Store Type")
    plt.grid(axis="y", linestyle="--", alpha=0.7)
    plt.show()
else:
    print(":x_red_circle: Error: The DataFrame does not contain all the necessary columns (district, selling_square_ft, type).")

df["selling_square_ft"] = df.groupby(["district", "type"])["selling_square_ft"].transform(lambda x: x.fillna(x.mean())).round(0)

df['expiring_date'] = df['expiring_date'].str.replace('-', '/', regex=False)

df['expiring_date']=pd.to_datetime(df['expiring_date'], format="%d/%m/%Y")

df['perc_expiring_sku']=round(df['perc_expiring_sku'],4)

df['Profit (€)']=round(df['Profit (€)'],4)

df['oldpvp']=round(df['oldpvp'],2)

df['discount']=round(df['discount'],3)

df['brand'] = df['brand'].replace(r'\s+', '', regex=True).str.upper()

df[['idstore', 'sku']] = df[['idstore', 'sku']].astype('object')
df['sold'] = df['sold'].astype('int64')

df[(df['sell_date'] < df['labelling_date']) | (df['expiring_date'] < df['labelling_date'])]

df.loc[(df['sell_date'] < df['labelling_date']) | (df['expiring_date'] < df['labelling_date']), 'sell_date'] = '2021-10-21'

df.loc[df['perc_expiring_sku']==2,'perc_expiring_sku'] = 1

df.drop(df[df["perc_expiring_sku"] == 1.8].index, inplace=True)

df[(df["oldpvp"] == 999) | (df["oldpvp"] == 500)]

df.drop(df[(df["oldpvp"] == 999) | (df["oldpvp"] == 500)].index, inplace=True)

df[(df.index == 550)]

df.loc[(df["discount"] == 0.99) & (df["sku"] == 4), "discount"] = 0.3
df.loc[(df["new_pvp"] == 0.0279) & (df["sku"] == 4), "new_pvp"] = 1.95

df['labelling_date'] = pd.to_datetime(df['labelling_date'])

df['sell_date'] = df['sell_date'].astype(str).str.replace('-', '/', regex=False)

df['sell_date'] = pd.to_datetime(df['sell_date'], infer_datetime_format=True, errors='coerce')

df['labelling_days_until_exp'] = (df['expiring_date'] - df['labelling_date']).dt.days

df['labelling_days_until_sales'] = np.where(
    pd.notna(df['sell_date']),
    (df['sell_date'] - df['labelling_date']).dt.days,
    999
)

df['sales_days_until_exp'] = np.where(
    pd.notna(df['sell_date']),
    (df['expiring_date'] - df['sell_date']).dt.days,
    999
)

df[['sales_days_until_exp', 'labelling_days_until_sales']] = df[['sales_days_until_exp', 'labelling_days_until_sales']].astype('int64')

mapeamento_regioes = {
    'Aveiro': 'Centro',
    'Beja': 'Sul',
    'Braga': 'Norte',
    'Bragança': 'Norte',
    'Castelo Branco': 'Centro',
    'Coimbra': 'Centro',
    'Évora': 'Sul',
    'Faro': 'Sul',
    'Guarda': 'Centro',
    'Leiria': 'Centro',
    'Lisboa': 'Sul',
    'Portalegre': 'Sul',
    'Porto': 'Norte',
    'Santarém': 'Centro',
    'Setúbal': 'Sul',
    'Viana do Castelo': 'Norte',
    'Vila Real': 'Norte',
    'Viseu': 'Centro'
}

df['region'] = df['district'].map(mapeamento_regioes)

marca2 = df[df['brand'] == 'MARCA2'].shape[0]
not_marca2 = df[df['brand'] != 'MARCA2'].shape[0]
total = df.shape[0]

percent_marca2 = (marca2 / total) * 100
percent_not_marca2 = (not_marca2 / total) * 100

print(f"Number of rows with 'MARCA2': {marca2} ({percent_marca2:.2f}%)")
print(f"Number of rows without 'MARCA2': {not_marca2} ({percent_not_marca2:.2f}%)")

Number of rows with 'MARCA2': 106129 (70.97%)
Number of rows without 'MARCA2': 43418 (29.03%)

df['marca_2'] = df['brand'].apply(lambda x: 'yes' if x == 'MARCA2' else 'no')

df['skus_per_ft'] = df.groupby('idstore')['sku'].transform('count') / df.groupby('idstore')['selling_square_ft'].transform('first')
df['skus_per_ft'] = df['skus_per_ft'].round(4)

df['skus_per_store'] = df.groupby('idstore')['sku'].transform('count')

df['labelling_day_of_week'] = df['labelling_date'].dt.dayofweek
df['labelling_day_of_week'] = df['labelling_day_of_week'].astype('category')

df['unique_skus_brand'] = df.groupby('brand')['sku'].transform('nunique')

df['product_cost'] = df['new_pvp'] * (1 - df['Margin (%)'] / 100)

df.drop('labelqty', axis=1, inplace=True)

df.drop('district', axis=1, inplace=True)

df.drop('Payment_method', axis=1, inplace=True)

df.drop('sell_date', axis=1, inplace=True)

df.drop('labelling_date', axis=1, inplace=True)

df.drop('expiring_date', axis=1, inplace=True)

df.drop('labelling_days_until_sales', axis=1, inplace=True)

df.drop('sales_days_until_exp', axis=1, inplace=True)

df.drop('idstore', axis=1, inplace=True)

df.drop('sku', axis=1, inplace=True)

df.drop('brand', axis=1, inplace=True)

cols = ['skus_per_ft', 'skus_per_store', 'unique_skus_brand', 'product_cost']

mean_values = df.groupby('region')[cols].mean()

fig, axes = plt.subplots(2, 2, figsize=(10, 6))

metric_titles = ['SKUs per square foot', 'SKUs per store', 'Unique SKUs per brand', 'Product cost']

for i, (ax, col, title) in enumerate(zip(axes.flatten(), cols, metric_titles)):
    bars = mean_values[col].plot(kind='bar', ax=ax, color='#001F69', width=0.7)
    ax.set_title(title)
    ax.set_xlabel('Region')
    ax.set_ylabel('Average')
    ax.set_xticklabels(mean_values.index, rotation=45)
    for j, value in enumerate(mean_values[col]):
        ax.text(j, value / 2, str(round(value, 2)), ha='center', va='center', color='white')

plt.suptitle('Average Distributions of the Created Variables', fontsize=14, color='black')
plt.tight_layout()
plt.show()

count_data = df.groupby(['type', df['sold'].replace({0: "Not Sold", 1: "Sold"}), 'region']).size().unstack(fill_value=0)

ax = count_data.plot(kind='barh', stacked=True, color=['#007473', '#001F69', '#FF6B00'], figsize=(8, 5))

for container in ax.containers:
    for bar in container:
        width = bar.get_width()
        if width > 0:
            x_pos = bar.get_x() + width / 2
            y_pos = bar.get_y() + bar.get_height() / 2
            ax.text(x_pos, y_pos, f'{int(width)}', ha='center', va='center', color='white', fontsize=10)

plt.xlabel("Count")
plt.ylabel("Sale Type and Status")
plt.title("Sold and Unsold Items by Store Type and Region")
plt.show()

day_mapping = {
    0: 'Monday',
    1: 'Tuesday',
    2: 'Wednesday',
    3: 'Thursday',
    4: 'Friday',
    5: 'Saturday',
    6: 'Sunday'
}

sales_by_day = df.groupby('labelling_day_of_week')['sold'].sum()

sales_by_day.index = sales_by_day.index.map(day_mapping)

plt.figure(figsize=(10, 6))
sales_by_day.plot(kind='bar', color='#001F69')

plt.title('Number of Sales by Day of the Week')
plt.xlabel('Day of the Week')
plt.ylabel('Number of Sales')

for i, value in enumerate(sales_by_day):
    plt.text(i, value + 200, str(value), ha='center')

plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

df.skew(numeric_only=True)

oldpvp                      5.145517
weight (g)                  0.524855
Margin (%)                 -0.371813
Profit (€)                  1.197624
perc_expiring_sku           0.207792
sold                       -0.148200
selling_square_ft           1.176166
new_pvp                     1.381423
discount                    3.376210
labelling_days_until_exp    6.855502
skus_per_ft                 6.398776
skus_per_store              2.182509
unique_skus_brand          -0.954862
product_cost                1.401147
dtype: float64

df.kurtosis(numeric_only=True)

oldpvp                      260.938506
weight (g)                    2.307579
Margin (%)                   -1.262098
Profit (€)                    3.816302
perc_expiring_sku            -0.420163
sold                         -1.978063
selling_square_ft             0.905701
new_pvp                       9.464847
discount                     24.742544
labelling_days_until_exp     71.062888
skus_per_ft                  41.785595
skus_per_store                6.518503
unique_skus_brand            -1.045601
product_cost                 10.355974
dtype: float64

def hist_boxplot_display(df, columns):
    for coluna in columns:
        fig, axes = plt.subplots(1, 3, figsize=(15, 3))  

        sns.histplot(df[coluna].dropna(), bins=30, kde=True, ax=axes[0])
        axes[0].set_title(f"Histogram - {coluna}")

        sns.boxplot(x=df[coluna].dropna(), ax=axes[1])
        axes[1].set_title(f"Boxplot - {coluna}")

        sns.kdeplot(df[coluna].dropna(), ax=axes[2], fill=True)
        axes[2].set_title(f"KDE - {coluna}")

        plt.tight_layout()
        plt.show()

columns = ['oldpvp', 'Profit (€)', 'selling_square_ft',
           'new_pvp' , 'discount', 'labelling_days_until_exp',
           'skus_per_ft','skus_per_store','weight (g)', 'unique_skus_brand','product_cost']

hist_boxplot_display(df, columns)

df_no_outliers = df.drop(df[df["oldpvp"] > 10].index)

hist_boxplot_display(df_no_outliers,['oldpvp'])

def scale_data_log(df, columns):
    df_scaled = df.copy()

    for column in columns:
        df_scaled[f"{column}_log_scaled"] = np.log1p(df_scaled[column])
    
    return df_scaled

def apply_log_scaling(df, columns, epsilon=0.000001):
    df1 = df.copy()
    for col in columns:
        if col in df1.columns:
            df1[col] =(df1[col] + epsilon)
    return df1

numerical_columns = df.select_dtypes(include='number').columns.tolist()

df_no_outliers=apply_log_scaling(df_no_outliers,['labelling_days_until_exp'])

columns = df_no_outliers.select_dtypes(include='number').columns.tolist()
df_scaled = scale_data_log(df_no_outliers, columns)

columns = df_scaled.select_dtypes(include='number').columns.tolist()

hist_boxplot_display(df_scaled, columns)

df_most_recent= df_no_outliers.copy()

skf = StratifiedKFold(n_splits = 7, random_state = 99, shuffle = True)

X = df_most_recent.drop('sold', axis = 1)
y = df_most_recent['sold'].copy()

X, X_test, y, y_test = train_test_split(X, y,
                                        train_size = 0.9,
                                        random_state = 99,
                                        stratify = y)

def apply_chisquare(X,y,var,alpha=0.05):
    dfObserved = pd.crosstab(y,X)
    chi2, p, dof, expected = stats.chi2_contingency(dfObserved.values)
    dfExpected = pd.DataFrame(expected, columns=dfObserved.columns, index = dfObserved.index)
    if p<alpha:
        result="{0} is IMPORTANT for the model".format(var)
    else:
        result="{0} is NOT IMPORTANT for the Forecast. (Discard {0} from the model)".format(var)
    print(result)

def select_best_cat_features(X,y):
    count = 1
    ############################################## APPLY SKF ######################################################
    for train_index, val_index in skf.split(X,y):
        X_train, X_val = X.iloc[train_index], X.iloc[val_index]
        y_train, y_val = y.iloc[train_index], y.iloc[val_index]
        #################################### SELECT FEATURES WITH CHI-SQUARE #######################################
        print('_________________________________________________________________________________________________\n')
        print('                                     SPLIT ' + str(count) + '                                    ')
        print('_________________________________________________________________________________________________')
        
        X_train_cat = X_train[['type','region','labelling_day_of_week','marca_2']].copy()
        for var in X_train_cat:
            apply_chisquare(X_train_cat[var],y_train, var)
        count+=1

select_best_cat_features(X,y)

_________________________________________________________________________________________________

                                     SPLIT 1                                    
_________________________________________________________________________________________________
type is IMPORTANT for the model
region is IMPORTANT for the model
labelling_day_of_week is IMPORTANT for the model
marca_2 is IMPORTANT for the model
_________________________________________________________________________________________________

                                     SPLIT 2                                    
_________________________________________________________________________________________________
type is IMPORTANT for the model
region is IMPORTANT for the model
labelling_day_of_week is IMPORTANT for the model
marca_2 is IMPORTANT for the model
_________________________________________________________________________________________________

                                     SPLIT 3                                    
_________________________________________________________________________________________________
type is IMPORTANT for the model
region is IMPORTANT for the model
labelling_day_of_week is IMPORTANT for the model
marca_2 is IMPORTANT for the model
_________________________________________________________________________________________________

                                     SPLIT 4                                    
_________________________________________________________________________________________________
type is IMPORTANT for the model
region is IMPORTANT for the model
labelling_day_of_week is IMPORTANT for the model
marca_2 is IMPORTANT for the model
_________________________________________________________________________________________________

                                     SPLIT 5                                    
_________________________________________________________________________________________________
type is IMPORTANT for the model
region is IMPORTANT for the model
labelling_day_of_week is IMPORTANT for the model
marca_2 is IMPORTANT for the model
_________________________________________________________________________________________________

                                     SPLIT 6                                    
_________________________________________________________________________________________________
type is IMPORTANT for the model
region is IMPORTANT for the model
labelling_day_of_week is IMPORTANT for the model
marca_2 is IMPORTANT for the model
_________________________________________________________________________________________________

                                     SPLIT 7                                    
_________________________________________________________________________________________________
type is IMPORTANT for the model
region is IMPORTANT for the model
labelling_day_of_week is IMPORTANT for the model
marca_2 is IMPORTANT for the model

def apply_variance(X_train):
    variances = X_train.var(numeric_only=True)

    print("Variance by variable (5 decimal places format):")
    print(variances.apply(lambda x: f"{x:.5f}"))

    return variances

def select_features_variance(X, y):
    results = []
    count = 1
    for train_index, val_index in skf.split(X, y):
        X_train = X.iloc[train_index]

        print('_________________________________________________________________________________________________\n')
        print(f'                                     SPLIT {count}                                    ')
        print('_________________________________________________________________________________________________')

        variances = apply_variance(X_train)
        results.append(variances)
        count += 1
    summary_df = pd.DataFrame(results).mean().reset_index()
    summary_df.columns = ["Variable", "Mean Variance"]
    summary_df["Mean Variance"] = summary_df["Mean Variance"].apply(lambda x: f"{x:.5f}")

    return summary_df

summary = select_features_variance(X, y)
print(summary)

_________________________________________________________________________________________________

                                     SPLIT 1                                    
_________________________________________________________________________________________________
Variance by variable (5 decimal places format):
oldpvp                             0.30534
weight (g)                      3326.56073
Margin (%)                        10.92776
Profit (€)                         0.00571
perc_expiring_sku                  0.04949
selling_square_ft           29712828.17834
new_pvp                            0.15054
discount                           0.00140
labelling_days_until_exp           1.75210
skus_per_ft                        7.88335
skus_per_store                148370.84467
unique_skus_brand               2460.79425
product_cost                       0.10560
dtype: object
_________________________________________________________________________________________________

                                     SPLIT 2                                    
_________________________________________________________________________________________________
Variance by variable (5 decimal places format):
oldpvp                             0.30602
weight (g)                      3327.95256
Margin (%)                        10.91391
Profit (€)                         0.00571
perc_expiring_sku                  0.04969
selling_square_ft           29850395.98619
new_pvp                            0.15088
discount                           0.00138
labelling_days_until_exp           1.73271
skus_per_ft                        7.85192
skus_per_store                147746.17974
unique_skus_brand               2459.35373
product_cost                       0.10585
dtype: object
_________________________________________________________________________________________________

                                     SPLIT 3                                    
_________________________________________________________________________________________________
Variance by variable (5 decimal places format):
oldpvp                             0.30693
weight (g)                      3324.50224
Margin (%)                        10.90126
Profit (€)                         0.00573
perc_expiring_sku                  0.04964
selling_square_ft           29838181.37070
new_pvp                            0.15126
discount                           0.00139
labelling_days_until_exp           1.73087
skus_per_ft                        7.83097
skus_per_store                147639.90887
unique_skus_brand               2450.90367
product_cost                       0.10607
dtype: object
_________________________________________________________________________________________________

                                     SPLIT 4                                    
_________________________________________________________________________________________________
Variance by variable (5 decimal places format):
oldpvp                             0.30756
weight (g)                      3326.93942
Margin (%)                        10.92621
Profit (€)                         0.00575
perc_expiring_sku                  0.04966
selling_square_ft           29757671.67629
new_pvp                            0.15175
discount                           0.00140
labelling_days_until_exp           1.76548
skus_per_ft                        7.84991
skus_per_store                147919.42065
unique_skus_brand               2453.18492
product_cost                       0.10639
dtype: object
_________________________________________________________________________________________________

                                     SPLIT 5                                    
_________________________________________________________________________________________________
Variance by variable (5 decimal places format):
oldpvp                             0.30658
weight (g)                      3323.25786
Margin (%)                        10.92256
Profit (€)                         0.00572
perc_expiring_sku                  0.04974
selling_square_ft           29882909.02742
new_pvp                            0.15103
discount                           0.00140
labelling_days_until_exp           1.72563
skus_per_ft                        7.85093
skus_per_store                148176.66677
unique_skus_brand               2460.63704
product_cost                       0.10596
dtype: object
_________________________________________________________________________________________________

                                     SPLIT 6                                    
_________________________________________________________________________________________________
Variance by variable (5 decimal places format):
oldpvp                             0.30693
weight (g)                      3331.31571
Margin (%)                        10.93291
Profit (€)                         0.00574
perc_expiring_sku                  0.04964
selling_square_ft           29722825.19063
new_pvp                            0.15118
discount                           0.00138
labelling_days_until_exp           1.72401
skus_per_ft                        7.85166
skus_per_store                148216.59951
unique_skus_brand               2456.86417
product_cost                       0.10601
dtype: object
_________________________________________________________________________________________________

                                     SPLIT 7                                    
_________________________________________________________________________________________________
Variance by variable (5 decimal places format):
oldpvp                             0.30740
weight (g)                      3333.99817
Margin (%)                        10.91068
Profit (€)                         0.00574
perc_expiring_sku                  0.04976
selling_square_ft           29763657.12647
new_pvp                            0.15148
discount                           0.00140
labelling_days_until_exp           1.74010
skus_per_ft                        7.85974
skus_per_store                148092.46283
unique_skus_brand               2459.69939
product_cost                       0.10620
dtype: object
                    Variable   Mean Variance
0                     oldpvp         0.30668
1                 weight (g)      3327.78952
2                 Margin (%)        10.91933
3                 Profit (€)         0.00573
4          perc_expiring_sku         0.04966
5          selling_square_ft  29789781.22229
6                    new_pvp         0.15116
7                   discount         0.00139
8   labelling_days_until_exp         1.73870
9                skus_per_ft         7.85407
10            skus_per_store    148023.15472
11         unique_skus_brand      2457.34817
12              product_cost         0.10601

X = X.drop(['Profit (€)', 'discount'], axis = 1)

def cor_heatmap(cor):
    plt.figure(figsize=(9,6))
    sns.heatmap(data = cor, annot = True, cmap = plt.cm.Reds, fmt='.1')
    plt.show()

def apply_correlation(X_train):
    correlation_data = X_train.copy()
    matrix = correlation_data.corr(method = 'spearman', numeric_only=True)
    cor_heatmap(matrix)

def redundant_features(X,y):
    count = 1
    for train_index, val_index in skf.split(X,y):
        X_train, X_val = X.iloc[train_index], X.iloc[val_index]
        y_train, y_val = y.iloc[train_index], y.iloc[val_index]

        ######################################### SELECT FEATURES #################################################        
        print('_________________________________________________________________________________________________\n')
        print('                                     SPLIT ' + str(count) + '                                    ')
        print('_________________________________________________________________________________________________')
        apply_correlation(X_train)
        count+=1

redundant_features(X, y)

_________________________________________________________________________________________________

                                     SPLIT 1                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 2                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 3                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 4                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 5                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 6                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 7                                    
_________________________________________________________________________________________________

def plot_importance(variables, name):
    imp_features = variables.sort_values()
    plt.figure(figsize=(3,3))
    ax = imp_features.plot(kind="barh")
    
    ax.spines['right'].set_visible(False)
    ax.spines['top'].set_visible(False)
    
    plt.title("Feature importance using " + name + " Model")
    ax.bar_label(ax.containers[0], fmt='%.2f', padding=0, fontsize=9, color='black')
       
    plt.show()

def apply_dt(X_train, y_train):
    dt = DecisionTreeClassifier(random_state = 99).fit(X_train, y_train)
    feature_importances = pd.Series(dt.feature_importances_, index = X_train.columns) 
    plot_importance(feature_importances, 'DT')

def select_best_features_dt(X, y):
    count = 1
    for train_index, val_index in skf.split(X,y):
        X_train, X_val = X.iloc[train_index], X.iloc[val_index]
        y_train, y_val = y.iloc[train_index], y.iloc[val_index]

        ######################################### SELECT FEATURES #################################################        
        print('_________________________________________________________________________________________________\n')
        print('                                     SPLIT ' + str(count) + '                                    ')
        print('_________________________________________________________________________________________________')

        X_train = X_train.drop(['type','region','labelling_day_of_week','marca_2'], axis = 1)
        apply_dt(X_train, y_train)
        
        count+=1

select_best_features_dt(X, y)

_________________________________________________________________________________________________

                                     SPLIT 1                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 2                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 3                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 4                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 5                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 6                                    
_________________________________________________________________________________________________

_________________________________________________________________________________________________

                                     SPLIT 7                                    
_________________________________________________________________________________________________

X = X.drop(['oldpvp','new_pvp','skus_per_ft','labelling_days_until_exp'], axis = 1)

def apply_rfe(X_train, y_train):
    rfe = RFE(estimator = LogisticRegression(), n_features_to_select = 4)
    rfe.fit_transform(X = X_train, y = y_train)
    selected_features = pd.Series(rfe.support_, index = X_train.columns)  
    print(selected_features)

_________________________________________________________________________________________________

                                     SPLIT 1                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

_________________________________________________________________________________________________

                                     SPLIT 2                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

_________________________________________________________________________________________________

                                     SPLIT 3                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

_________________________________________________________________________________________________

                                     SPLIT 4                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

_________________________________________________________________________________________________

                                     SPLIT 5                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

def apply_rfe(X_train, y_train):
    rfe = RFE(estimator = LogisticRegression(), n_features_to_select = 4)
    rfe.fit_transform(X = X_train, y = y_train)
    selected_features = pd.Series(rfe.support_, index = X_train.columns)  
    print(selected_features)

def apply_lasso(X_train, y_train):
    lasso = LogisticRegressionCV(penalty='l1', solver='saga').fit(X_train, y_train)
    coef = pd.Series(abs(lasso.coef_[0]), index = X_train.columns)
    plot_importance(coef,'Lasso')

def select_best_features(X,y):
    count = 1
    for train_index, val_index in skf.split(X,y):
        X_train, X_val = X.iloc[train_index], X.iloc[val_index]
        y_train, y_val = y.iloc[train_index], y.iloc[val_index]

        
        ########################################### SCALE DATA #################################################### 
        numerical_data = X_train.drop(['type','region','marca_2','labelling_day_of_week'], axis = 1).copy()
        X_train_scaled = np.log1p(numerical_data)
        X_train_scaled = pd.DataFrame(X_train_scaled, columns = numerical_data.columns)
    
        ######################################### SELECT FEATURES #################################################        
        print('_________________________________________________________________________________________________\n')
        print('                                     SPLIT ' + str(count) + '                                    ')
        print('_________________________________________________________________________________________________')
        
        # Check which features to use using RFE
        print('')
        print('----------------- RFE ----------------------')
        apply_rfe(X_train_scaled, y_train)
        
        # check which features to use using lasso
        print('')
        print('----------------- LASSO ----------------------')
        apply_lasso(X_train_scaled, y_train)
            
        # check which features to use using lasso
        print('')
        print('----------------- DT ----------------------')
        apply_dt(X_train_scaled, y_train)
            
        count+=1

select_best_features(X, y)

_________________________________________________________________________________________________

                                     SPLIT 1                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

_________________________________________________________________________________________________

                                     SPLIT 2                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

_________________________________________________________________________________________________

                                     SPLIT 3                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

_________________________________________________________________________________________________

                                     SPLIT 4                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

_________________________________________________________________________________________________

                                     SPLIT 5                                    
_________________________________________________________________________________________________

----------------- RFE ----------------------
weight (g)           False
Margin (%)           False
perc_expiring_sku     True
selling_square_ft    False
skus_per_store        True
unique_skus_brand     True
product_cost          True
dtype: bool

----------------- LASSO ----------------------

----------------- DT ----------------------

keep_data = df_most_recent[['type','region','labelling_day_of_week','marca_2','skus_per_store','unique_skus_brand','product_cost','sold']].copy()	
all_data = df_most_recent[['type','region','labelling_day_of_week','marca_2','skus_per_store','unique_skus_brand','product_cost','perc_expiring_sku','selling_square_ft','sold']].copy()

X_keep_data = keep_data.iloc[:,:-1]
y_keep_data = keep_data.iloc[:,-1]

X, X_test, y, y_test = train_test_split(X_keep_data, y_keep_data, 
                                        train_size = 0.9, 
                                        random_state = 99, 
                                        stratify = y_keep_data)
keep_data = pd.concat([X, y], axis=1)

X_all_data = all_data.iloc[:,:-1]
y_all_data = all_data.iloc[:,-1]
X, X_test, y, y_test = train_test_split(X_all_data, y_all_data,           
                                        train_size = 0.9, 
                                        random_state = 99, 
                                        stratify = y_all_data)
all_data = pd.concat([X, y], axis=1)

def transform_data(X_train, X_2nd_df = False, X_2nd_df_flag = False):
    
    ########################################## CREATE DUMMIES #################################################    
    X_train_dummies = pd.get_dummies(X_train)
    X_train_dummies.drop('region_Sul', axis = 1, inplace = True)
    X_train_dummies.drop('type_Medium', axis = 1, inplace = True)
    X_train_dummies.drop('marca_2_yes', axis = 1, inplace = True)
    X_train_dummies.drop('labelling_day_of_week_3', axis = 1, inplace = True)
    
    ############################################ SCALE DATA ###################################################    
    scaler = FunctionTransformer(np.log1p)
    X_train_scaled = scaler.fit_transform(X_train_dummies)
    X_train_scaled = np.log1p(X_train_dummies)
    X_train_scaled = pd.DataFrame(X_train_scaled , columns = X_train_dummies.columns)
    
    if X_2nd_df_flag == True: 
        
        ########################################## CREATE DUMMIES #############################################
        X_2nd_df_dummies = pd.get_dummies(X_2nd_df)
        X_2nd_df_dummies.drop('region_Sul', axis = 1, inplace = True)
        X_2nd_df_dummies.drop('type_Medium', axis = 1, inplace = True)
        X_2nd_df_dummies.drop('marca_2_yes', axis = 1, inplace = True)
        X_2nd_df_dummies.drop('labelling_day_of_week_3', axis = 1, inplace = True)
        missing_cols = set(X_train_dummies.columns ) - set(X_2nd_df_dummies.columns )
        
        for c in missing_cols:
            X_2nd_df_dummies[c] = 0
        
        X_2nd_df_dummies = X_2nd_df_dummies[X_train_dummies.columns]
        
        if 'sold' in X_2nd_df_dummies.columns:
            X_2nd_df_dummies.drop(['sold'], axis=1, inplace=True)
        else:
            pass
        
        ############################################ SCALE DATA ###################################################    
        X_2nd_df_scaled = scaler.transform(X_2nd_df_dummies)
        X_2nd_df_scaled = pd.DataFrame(X_2nd_df_scaled , columns = X_2nd_df_dummies.columns, index = X_2nd_df_dummies.index )
        
   
    if X_2nd_df_flag == False:
        return X_train_scaled
    else:
        return X_train_scaled, X_2nd_df_scaled

def select_best_models(data, model):
    skf = StratifiedKFold(n_splits=7, random_state=99, shuffle=True)
    X = data.drop('sold', axis=1)
    y = data['sold'].copy()

    metrics = {
        "f1_train": [], "f1_val": [],
        "recall_train": [], "recall_val": [],
        "precision_train": [], "precision_val": [],
        "accuracy_train": [], "accuracy_val": [],
        "time": []
    }
    
    for train_index, val_index in skf.split(X, y):
        X_train, X_val = X.iloc[train_index], X.iloc[val_index]
        y_train, y_val = y.iloc[train_index], y.iloc[val_index]
        
        X_train, X_val = transform_data(X_train, X_val, X_2nd_df_flag=True)
        
        start_time = time.time()
        model.fit(X_train, y_train)
        predictions_train = model.predict(X_train)
        predictions_val = model.predict(X_val)
        elapsed_time = time.time() - start_time
        
        metrics["f1_train"].append(f1_score(y_train, predictions_train, average="weighted"))
        metrics["f1_val"].append(f1_score(y_val, predictions_val, average="weighted"))
        metrics["recall_train"].append(recall_score(y_train, predictions_train, average="weighted"))
        metrics["recall_val"].append(recall_score(y_val, predictions_val, average="weighted"))
        metrics["precision_train"].append(precision_score(y_train, predictions_train, average="weighted"))
        metrics["precision_val"].append(precision_score(y_val, predictions_val, average="weighted"))
        metrics["accuracy_train"].append(accuracy_score(y_train, predictions_train))
        metrics["accuracy_val"].append(accuracy_score(y_val, predictions_val))
        metrics["time"].append(elapsed_time)

    avg_metrics = {key: round(np.mean(values), 4) for key, values in metrics.items()}
    std_metrics = {key: round(np.std(values), 4) for key, values in metrics.items()}

    return avg_metrics, std_metrics

def show_results(df, data, *args):
    count = 0
    for arg in args:
        avg_metrics, std_metrics = select_best_models(data, arg)
        
        df.iloc[count] = [
            f"{avg_metrics['f1_train']}+/-{std_metrics['f1_train']}",
            f"{avg_metrics['f1_val']}+/-{std_metrics['f1_val']}",
            f"{avg_metrics['recall_train']}+/-{std_metrics['recall_train']}",
            f"{avg_metrics['recall_val']}+/-{std_metrics['recall_val']}",
            f"{avg_metrics['precision_train']}+/-{std_metrics['precision_train']}",
            f"{avg_metrics['precision_val']}+/-{std_metrics['precision_val']}",
            f"{avg_metrics['accuracy_train']}+/-{std_metrics['accuracy_train']}",
            f"{avg_metrics['accuracy_val']}+/-{std_metrics['accuracy_val']}",
            f"{avg_metrics['time']}"
        ]
        count += 1
    return df

model_LR = LogisticRegression(max_iter=1000)
model_KNN = KNeighborsClassifier(n_neighbors=5, weights="distance", p=1)
model_DT = DecisionTreeClassifier(max_depth = 15, min_samples_split = 10, min_samples_leaf=5,class_weight="balanced", random_state = 99)
model_RFC = RandomForestClassifier(max_depth=20, min_samples_split=4, n_estimators=200,max_features='sqrt', random_state=99)
model_NB = GaussianNB()
model_GBC = GradientBoostingClassifier(n_estimators=100, learning_rate=0.05, max_depth=3, min_samples_split=5, min_samples_leaf=3, subsample=0.8, random_state=99) 
model_ABC = AdaBoostClassifier(estimator=DecisionTreeClassifier(max_depth=2), n_estimators=100, learning_rate=0.5, random_state=99)
model_SVC = SVC(C=0.1, kernel='linear')
model_MLP = MLPClassifier()

df_all = pd.DataFrame(columns=['F1 Train', 'F1 Validation', 'Recall Train', 'Recall Validation',
                               'Precision Train', 'Precision Validation', 'Accuracy Train', 
                               'Accuracy Validation', 'Running Time'],
                      index=['Logistic Regression', 'KNN', 'DT', 'RFC', 'NB','GBC', 'ABC','SVC','MLP'])

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 17, 'min_samples_leaf': 5, 'min_samples_split': 5}
Best Score:  0.7247003878952627

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 20, 'max_features': 'sqrt', 'min_samples_split': 4, 'n_estimators': 100}
Best Score:  0.751092837147111

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

Best Threshold=0.373164, F-Score=0.758

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

df_all = pd.DataFrame(columns=['F1 Train', 'F1 Validation', 'Recall Train', 'Recall Validation',
                               'Precision Train', 'Precision Validation', 'Accuracy Train', 
                               'Accuracy Validation', 'Running Time'],
                      index=['Logistic Regression', 'KNN', 'DT', 'RFC', 'NB','GBC', 'ABC','SVC','MLP'])

show_results(df_all, all_data, model_LR, model_KNN, model_DT, model_RFC, model_NB, model_GBC, model_ABC, model_SVC, model_MLP)

df_keep = pd.DataFrame(columns=['F1 Train', 'F1 Validation', 'Recall Train', 'Recall Validation',
                               'Precision Train', 'Precision Validation', 'Accuracy Train', 
                               'Accuracy Validation', 'Running Time'],
                      index=['Logistic Regression', 'KNN', 'DT', 'RFC', 'NB','GBC', 'ABC','SVC','MLP'])

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 17, 'min_samples_leaf': 5, 'min_samples_split': 5}
Best Score:  0.7247003878952627

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 20, 'max_features': 'sqrt', 'min_samples_split': 4, 'n_estimators': 100}
Best Score:  0.751092837147111

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

Best Threshold=0.373164, F-Score=0.758

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

df_keep = pd.DataFrame(columns=['F1 Train', 'F1 Validation', 'Recall Train', 'Recall Validation',
                               'Precision Train', 'Precision Validation', 'Accuracy Train', 
                               'Accuracy Validation', 'Running Time'],
                      index=['Logistic Regression', 'KNN', 'DT', 'RFC', 'NB','GBC', 'ABC','SVC','MLP'])

show_results(df_keep, keep_data, model_LR, model_KNN, model_DT, model_RFC, model_NB, model_GBC, model_ABC, model_SVC, model_MLP)

def point_plot(train, validation, values_try):
    plt.figure(figsize=(8, 5))

    sns.pointplot(x=values_try, y=train.values, color='teal', label='Train')
    sns.pointplot(x=values_try, y=validation.values, color='goldenrod', label='Validation')

    plt.title("F1 Score vs. Number of Neighbors")
    plt.xlabel("Number of Neighbors (k)")
    plt.ylabel("F1 Score")
    plt.legend()
    plt.show()

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 17, 'min_samples_leaf': 5, 'min_samples_split': 5}
Best Score:  0.7247003878952627

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 20, 'max_features': 'sqrt', 'min_samples_split': 4, 'n_estimators': 100}
Best Score:  0.751092837147111

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

Best Threshold=0.373164, F-Score=0.758

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

def point_plot(train, validation, values_try):
    plt.figure(figsize=(8, 5))

    sns.pointplot(x=values_try, y=train.values, color='teal', label='Train')
    sns.pointplot(x=values_try, y=validation.values, color='goldenrod', label='Validation')

    plt.title("F1 Score vs. Number of Neighbors")
    plt.xlabel("Number of Neighbors (k)")
    plt.ylabel("F1 Score")
    plt.legend()
    plt.show()

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 17, 'min_samples_leaf': 5, 'min_samples_split': 5}
Best Score:  0.7247003878952627

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 20, 'max_features': 'sqrt', 'min_samples_split': 4, 'n_estimators': 100}
Best Score:  0.751092837147111

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

Best Threshold=0.373164, F-Score=0.758

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

def point_plot(train, validation, values_try):
    plt.figure(figsize=(8, 5))

    sns.pointplot(x=values_try, y=train.values, color='teal', label='Train')
    sns.pointplot(x=values_try, y=validation.values, color='goldenrod', label='Validation')

    plt.title("F1 Score vs. Number of Neighbors")
    plt.xlabel("Number of Neighbors (k)")
    plt.ylabel("F1 Score")
    plt.legend()
    plt.show()

def get_models_knn(data, values):
    results_avg = {}
    results_std = {}
    for value in values:
        model = KNeighborsClassifier(n_neighbors=value)
        try:
            avg_metrics, std_metrics = select_best_models(data, model)
            results_avg[f'neighbors_{value}'] = avg_metrics
            results_std[f'neighbors_{value}'] = std_metrics
        except Exception as e:
            print(f"Erro ao processar neighbors_{value}: {e}")
    return results_avg, results_std

values_try = [5,8,11,14,20]
results_train, results_val = get_models_knn(all_data, values_try)
df_results = pd.DataFrame.from_dict(results_train, orient="index")
df_results_val = pd.DataFrame.from_dict(results_val, orient="index")
point_plot(df_results["f1_train"], df_results["f1_val"], values_try)

final_model_knn = KNeighborsClassifier(n_neighbors = 8)

data = all_data.copy()

X_data = data.drop(['sold'], axis = 1)
y_data = data['sold'].copy()

X_data = transform_data(X_train = X_data, X_2nd_df_flag = False)

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 17, 'min_samples_leaf': 5, 'min_samples_split': 5}
Best Score:  0.7247003878952627

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 20, 'max_features': 'sqrt', 'min_samples_split': 4, 'n_estimators': 100}
Best Score:  0.751092837147111

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

Best Threshold=0.373164, F-Score=0.758

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

data = all_data.copy()

X_data = data.drop(['sold'], axis = 1)
y_data = data['sold'].copy()

X_data = transform_data(X_train = X_data, X_2nd_df_flag = False)

dt = DecisionTreeClassifier(max_depth = 2, random_state = 99)

parameter_space_grid = {
    'criterion': ['gini', 'entropy'],
    'max_depth': [5,10,12,15,17],
    'min_samples_split': [5, 8,10,15],
    'min_samples_leaf':[5,10,15]
}

grid_search = GridSearchCV(dt, parameter_space_grid, scoring = 'f1', return_train_score = True, cv = 7)

grid_search.fit(X_data, y_data)

print("Best Hyperparameters: ", grid_search.best_params_)
print("Best Score: ", grid_search.best_score_)

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 17, 'min_samples_leaf': 5, 'min_samples_split': 5}
Best Score:  0.7247003878952627

final_model_dt = DecisionTreeClassifier(criterion = 'entropy', max_depth = 17, min_samples_leaf=5, min_samples_split = 5)

rf = RandomForestClassifier(max_depth=20, min_samples_split=4,n_estimators=200, max_features='sqrt',random_state=99)

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 20, 'max_features': 'sqrt', 'min_samples_split': 4, 'n_estimators': 100}
Best Score:  0.751092837147111

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

Best Threshold=0.373164, F-Score=0.758

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Euclidean Distances Between Cluster Centroids:
[[0.         2.58797236]
 [2.58797236 0.        ]]
Mean of each variable per cluster (K = 2, not scaled):
label                           0          1
sold                     0.337529   0.618382
type_Medium              0.461763   0.397868
type_Small               0.301254   0.453009
marca_2_yes              0.004563   0.997374
unique_skus_brand_sqrt   3.599177  11.074450
product_cost_sqrt        1.037887   0.907283
perc_expiring_sku_sqrt   0.604951   0.561889
selling_square_ft_sqrt  73.968193  62.661564
Euclidean Distances Between Cluster Centroids:
[[0.         2.04607656 2.94494721]
 [2.04607656 0.         2.65822457]
 [2.94494721 2.65822457 0.        ]]
Mean of each variable per cluster (K = 3, not scaled):
label                           0          1          2
sold                     0.606257   0.632925   0.322645
type_Medium              0.060445   0.708554   0.472006
type_Small               0.939044   0.000000   0.291538
marca_2_yes              0.978063   0.989929   0.000905
unique_skus_brand_sqrt  10.933370  11.030199   3.550519
product_cost_sqrt        0.897487   0.912418   1.048247
perc_expiring_sku_sqrt   0.555588   0.570960   0.602379
selling_square_ft_sqrt  30.838581  92.501152  74.418043
Euclidean Distances Between Cluster Centroids:
[[0.         2.2752584  2.65570749 2.26599293]
 [2.2752584  0.         2.91793035 2.07083357]
 [2.65570749 2.91793035 0.         3.1935877 ]
 [2.26599293 2.07083357 3.1935877  0.        ]]
Mean of each variable per cluster (K = 4, not scaled):
label                           0          1          2          3
sold                     0.000000   0.997024   0.316575   1.000000
type_Medium              0.397718   0.691756   0.472879   0.061176
type_Small               0.471092   0.000000   0.289265   0.938789
marca_2_yes              0.989700   0.978074   0.001133   0.965649
unique_skus_brand_sqrt  11.024179  10.957569   3.539862  10.837545
product_cost_sqrt        0.908666   0.908622   1.052253   0.894349
perc_expiring_sku_sqrt   0.524476   0.590855   0.604153   0.581716
selling_square_ft_sqrt  61.067387  93.421199  74.660637  30.372750

rf = RandomForestClassifier(max_depth=20, min_samples_split=4,n_estimators=200, max_features='sqrt',random_state=99)

parameter_space_grid_rf = {
    'criterion': ['gini', 'entropy'],
    'max_depth': [5,10,12,17,20],
    'min_samples_split': [4, 7, 11, 14],
    'n_estimators': [100, 200, 300, 500],
    'max_features': ['auto', 'sqrt', 'log2'],
}

grid_search_rf = GridSearchCV(rf, parameter_space_grid_rf, scoring = 'f1', return_train_score = True, cv = 7)

grid_search_rf.fit(X_data, y_data)

print("Best Hyperparameters: ", grid_search_rf.best_params_)
print("Best Score: ", grid_search_rf.best_score_)

Best Hyperparameters:  {'criterion': 'entropy', 'max_depth': 20, 'max_features': 'sqrt', 'min_samples_split': 4, 'n_estimators': 100}
Best Score:  0.751092837147111

final_model_rf = RandomForestClassifier(
    criterion='entropy', 
    max_depth=20, 
    min_samples_split=4, 
    max_features='sqrt', 
    n_estimators=100
)

df_final_models = pd.DataFrame(columns = ['F1 Train', 'F1 Validation', 'Recall Train', 'Recall Validation',
                               'Precision Train', 'Precision Validation', 'Accuracy Train', 
                               'Accuracy Validation', 'Running Time'], index = ['Best KNN','Best DT','Best RFC'])
show_results(df_final_models, all_data, final_model_knn, final_model_dt,final_model_rf)

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

Best Threshold=0.373164, F-Score=0.758

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Euclidean Distances Between Cluster Centroids:
[[0.         2.58797236]
 [2.58797236 0.        ]]
Mean of each variable per cluster (K = 2, not scaled):
label                           0          1
sold                     0.337529   0.618382
type_Medium              0.461763   0.397868
type_Small               0.301254   0.453009
marca_2_yes              0.004563   0.997374
unique_skus_brand_sqrt   3.599177  11.074450
product_cost_sqrt        1.037887   0.907283
perc_expiring_sku_sqrt   0.604951   0.561889
selling_square_ft_sqrt  73.968193  62.661564
Euclidean Distances Between Cluster Centroids:
[[0.         2.04607656 2.94494721]
 [2.04607656 0.         2.65822457]
 [2.94494721 2.65822457 0.        ]]
Mean of each variable per cluster (K = 3, not scaled):
label                           0          1          2
sold                     0.606257   0.632925   0.322645
type_Medium              0.060445   0.708554   0.472006
type_Small               0.939044   0.000000   0.291538
marca_2_yes              0.978063   0.989929   0.000905
unique_skus_brand_sqrt  10.933370  11.030199   3.550519
product_cost_sqrt        0.897487   0.912418   1.048247
perc_expiring_sku_sqrt   0.555588   0.570960   0.602379
selling_square_ft_sqrt  30.838581  92.501152  74.418043
Euclidean Distances Between Cluster Centroids:
[[0.         2.2752584  2.65570749 2.26599293]
 [2.2752584  0.         2.91793035 2.07083357]
 [2.65570749 2.91793035 0.         3.1935877 ]
 [2.26599293 2.07083357 3.1935877  0.        ]]
Mean of each variable per cluster (K = 4, not scaled):
label                           0          1          2          3
sold                     0.000000   0.997024   0.316575   1.000000
type_Medium              0.397718   0.691756   0.472879   0.061176
type_Small               0.471092   0.000000   0.289265   0.938789
marca_2_yes              0.989700   0.978074   0.001133   0.965649
unique_skus_brand_sqrt  11.024179  10.957569   3.539862  10.837545
product_cost_sqrt        0.908666   0.908622   1.052253   0.894349
perc_expiring_sku_sqrt   0.524476   0.590855   0.604153   0.581716
selling_square_ft_sqrt  61.067387  93.421199  74.660637  30.372750

Mean of each variable per cluster (K = 3, unscaled):

df_final_models = pd.DataFrame(columns = ['F1 Train', 'F1 Validation', 'Recall Train', 'Recall Validation',
                               'Precision Train', 'Precision Validation', 'Accuracy Train', 
                               'Accuracy Validation', 'Running Time'], index = ['Best KNN','Best DT','Best RFC'])
show_results(df_final_models, all_data, final_model_knn, final_model_dt,final_model_rf)

data = all_data.copy()

X_data = data.iloc[:,:-1]
y_data = data.iloc[:,-1]

X_train, X_val, y_train, y_val = train_test_split(X_data, y_data,
                                                  train_size = 0.9, 
                                                  random_state = 99, 
                                                  stratify = y_data)

X_train, X_val = transform_data(X_train, X_val, X_2nd_df_flag = True)

model_knn = final_model_knn.fit(X_train, y_train)
model_dt = final_model_dt.fit(X_train, y_train)
model_rf = final_model_rf.fit(X_train, y_train)

prob_modelKNN = model_knn.predict_proba(X_val)
prob_modelDT =  model_dt.predict_proba(X_val)
prob_modelRF =  model_rf.predict_proba(X_val)

fpr_modelKNN, tpr_modelKNN, thresholds_modelKNN = roc_curve(y_true = y_val, y_score = prob_modelKNN[:,1])
fpr_modelDT, tpr_modelDT, thresholds_modelDT = roc_curve(y_val, prob_modelDT[:,1])
fpr_modelRF, tpr_modelRF, thresholds_modelRF = roc_curve(y_val, prob_modelRF[:,1])

plt.plot(fpr_modelKNN, tpr_modelKNN,label="ROC Curve KNN")
plt.plot(fpr_modelDT, tpr_modelDT, label="ROC Curve DT")
plt.plot(fpr_modelRF, tpr_modelRF, label="ROC Curve RF")
plt.xlabel('FPR')
plt.ylabel('TPR')
plt.legend()
plt.show()

final_model = final_model_rf.fit(X_train, y_train)

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

Best Threshold=0.373164, F-Score=0.758

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Euclidean Distances Between Cluster Centroids:
[[0.         2.58797236]
 [2.58797236 0.        ]]
Mean of each variable per cluster (K = 2, not scaled):
label                           0          1
sold                     0.337529   0.618382
type_Medium              0.461763   0.397868
type_Small               0.301254   0.453009
marca_2_yes              0.004563   0.997374
unique_skus_brand_sqrt   3.599177  11.074450
product_cost_sqrt        1.037887   0.907283
perc_expiring_sku_sqrt   0.604951   0.561889
selling_square_ft_sqrt  73.968193  62.661564
Euclidean Distances Between Cluster Centroids:
[[0.         2.04607656 2.94494721]
 [2.04607656 0.         2.65822457]
 [2.94494721 2.65822457 0.        ]]
Mean of each variable per cluster (K = 3, not scaled):
label                           0          1          2
sold                     0.606257   0.632925   0.322645
type_Medium              0.060445   0.708554   0.472006
type_Small               0.939044   0.000000   0.291538
marca_2_yes              0.978063   0.989929   0.000905
unique_skus_brand_sqrt  10.933370  11.030199   3.550519
product_cost_sqrt        0.897487   0.912418   1.048247
perc_expiring_sku_sqrt   0.555588   0.570960   0.602379
selling_square_ft_sqrt  30.838581  92.501152  74.418043
Euclidean Distances Between Cluster Centroids:
[[0.         2.2752584  2.65570749 2.26599293]
 [2.2752584  0.         2.91793035 2.07083357]
 [2.65570749 2.91793035 0.         3.1935877 ]
 [2.26599293 2.07083357 3.1935877  0.        ]]
Mean of each variable per cluster (K = 4, not scaled):
label                           0          1          2          3
sold                     0.000000   0.997024   0.316575   1.000000
type_Medium              0.397718   0.691756   0.472879   0.061176
type_Small               0.471092   0.000000   0.289265   0.938789
marca_2_yes              0.989700   0.978074   0.001133   0.965649
unique_skus_brand_sqrt  11.024179  10.957569   3.539862  10.837545
product_cost_sqrt        0.908666   0.908622   1.052253   0.894349
perc_expiring_sku_sqrt   0.524476   0.590855   0.604153   0.581716
selling_square_ft_sqrt  61.067387  93.421199  74.660637  30.372750

Mean of each variable per cluster (K = 3, unscaled):

final_model = final_model_rf.fit(X_train, y_train)

predict_proba = final_model.predict_proba(X_val)
predict_proba

array([[0.25954191, 0.74045809],
       [0.81459037, 0.18540963],
       [0.61632967, 0.38367033],
       ...,
       [0.37535445, 0.62464555],
       [0.27381607, 0.72618393],
       [0.7154915 , 0.2845085 ]])

precision, recall, thresholds = precision_recall_curve(y_val, predict_proba[:,1])

fscore = (2 * precision * recall) / (precision + recall)
ix = np.argmax(fscore)
print('Best Threshold=%f, F-Score=%.3f' % (thresholds[ix], fscore[ix]))

plt.plot(recall, precision, marker='.', label='RFC', linewidth=0.5)
plt.scatter(recall[ix], precision[ix], marker='o', color='black', label='Best', s=100)
plt.xlabel('Recall')
plt.ylabel('Precision')
plt.legend()
plt.show()

Best Threshold=0.373164, F-Score=0.758

threshold = 0.373164

train = all_data.copy()

X_train = all_data.drop(['sold'], axis = 1)
y_train = all_data['sold']

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

0.7595634320293927

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

Euclidean Distances Between Cluster Centroids:
[[0.         2.58797236]
 [2.58797236 0.        ]]
Mean of each variable per cluster (K = 2, not scaled):
label                           0          1
sold                     0.337529   0.618382
type_Medium              0.461763   0.397868
type_Small               0.301254   0.453009
marca_2_yes              0.004563   0.997374
unique_skus_brand_sqrt   3.599177  11.074450
product_cost_sqrt        1.037887   0.907283
perc_expiring_sku_sqrt   0.604951   0.561889
selling_square_ft_sqrt  73.968193  62.661564
Euclidean Distances Between Cluster Centroids:
[[0.         2.04607656 2.94494721]
 [2.04607656 0.         2.65822457]
 [2.94494721 2.65822457 0.        ]]
Mean of each variable per cluster (K = 3, not scaled):
label                           0          1          2
sold                     0.606257   0.632925   0.322645
type_Medium              0.060445   0.708554   0.472006
type_Small               0.939044   0.000000   0.291538
marca_2_yes              0.978063   0.989929   0.000905
unique_skus_brand_sqrt  10.933370  11.030199   3.550519
product_cost_sqrt        0.897487   0.912418   1.048247
perc_expiring_sku_sqrt   0.555588   0.570960   0.602379
selling_square_ft_sqrt  30.838581  92.501152  74.418043
Euclidean Distances Between Cluster Centroids:
[[0.         2.2752584  2.65570749 2.26599293]
 [2.2752584  0.         2.91793035 2.07083357]
 [2.65570749 2.91793035 0.         3.1935877 ]
 [2.26599293 2.07083357 3.1935877  0.        ]]
Mean of each variable per cluster (K = 4, not scaled):
label                           0          1          2          3
sold                     0.000000   0.997024   0.316575   1.000000
type_Medium              0.397718   0.691756   0.472879   0.061176
type_Small               0.471092   0.000000   0.289265   0.938789
marca_2_yes              0.989700   0.978074   0.001133   0.965649
unique_skus_brand_sqrt  11.024179  10.957569   3.539862  10.837545
product_cost_sqrt        0.908666   0.908622   1.052253   0.894349
perc_expiring_sku_sqrt   0.524476   0.590855   0.604153   0.581716
selling_square_ft_sqrt  61.067387  93.421199  74.660637  30.372750

Mean of each variable per cluster (K = 3, unscaled):

train = all_data.copy()

X_train = all_data.drop(['sold'], axis = 1)
y_train = all_data['sold']

X, X_test, y, y_test = train_test_split(X_all_data, y_all_data,           
                                        train_size = 0.9, 
                                        random_state = 99, 
                                        stratify = y_all_data)

X_train, X_test = transform_data(X_train, X_test, X_2nd_df_flag = True)

final_model = final_model_rf.fit(X_train, y_train)

final_model.predict(X_test)

array([1, 1, 1, ..., 1, 0, 1], dtype=int64)

test_pred = []

for value in predict_proba_test[:,1]:
    if (value>=threshold):
        test_pred.append(1)
    else:
        test_pred.append(0)

f1_score(y_test, test_pred)

0.7595634320293927

df_clustering = all_data.copy()

df_clustering.columns

Index(['type', 'region', 'labelling_day_of_week', 'marca_2', 'skus_per_store',
       'unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold'],
      dtype='object')

df_clustering.drop(['region', 'labelling_day_of_week', 'skus_per_store'], axis=1, inplace=True)

df_clust_dummies = pd.get_dummies(df_clustering, columns=['type','marca_2'], drop_first=True)

df_clust_dummies.columns

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small',
       'marca_2_yes'],
      dtype='object')

assymetric_variables = df_clust_dummies[['unique_skus_brand', 'product_cost',
       'perc_expiring_sku', 'selling_square_ft']]

for col in assymetric_variables.columns:
    df_clust_dummies[f"{col}_sqrt"] = np.sqrt(df_clust_dummies[col].clip(lower=0))

df_clust_dummies.describe()

df_clust_dummies.columns

Index(['unique_skus_brand', 'product_cost', 'perc_expiring_sku',
       'selling_square_ft', 'sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

df_clust_dummies.drop(['unique_skus_brand', 'product_cost',
       'perc_expiring_sku', 'selling_square_ft'], axis = 1, inplace = True)

numeric_cols = df_clust_dummies.select_dtypes(include=['float64', 'int64']).columns
scaler = StandardScaler()

df_clust_dummies_scaled = df_clust_dummies.copy()
df_clust_dummies_scaled[numeric_cols] = scaler.fit_transform(df_clust_dummies_scaled[numeric_cols])

df_clust_dummies_scaled.columns

Index(['sold', 'type_Medium', 'type_Small', 'marca_2_yes',
       'unique_skus_brand_sqrt', 'product_cost_sqrt', 'perc_expiring_sku_sqrt',
       'selling_square_ft_sqrt'],
      dtype='object')

ks = range(1, 11)
inertias = []

for k in ks:
    model = KMeans(n_clusters=k).fit(df_clust_dummies_scaled)
    inertias.append(model.inertia_)

Euclidean Distances Between Cluster Centroids:
[[0.         2.58797236]
 [2.58797236 0.        ]]
Mean of each variable per cluster (K = 2, not scaled):
label                           0          1
sold                     0.337529   0.618382
type_Medium              0.461763   0.397868
type_Small               0.301254   0.453009
marca_2_yes              0.004563   0.997374
unique_skus_brand_sqrt   3.599177  11.074450
product_cost_sqrt        1.037887   0.907283
perc_expiring_sku_sqrt   0.604951   0.561889
selling_square_ft_sqrt  73.968193  62.661564
Euclidean Distances Between Cluster Centroids:
[[0.         2.04607656 2.94494721]
 [2.04607656 0.         2.65822457]
 [2.94494721 2.65822457 0.        ]]
Mean of each variable per cluster (K = 3, not scaled):
label                           0          1          2
sold                     0.606257   0.632925   0.322645
type_Medium              0.060445   0.708554   0.472006
type_Small               0.939044   0.000000   0.291538
marca_2_yes              0.978063   0.989929   0.000905
unique_skus_brand_sqrt  10.933370  11.030199   3.550519
product_cost_sqrt        0.897487   0.912418   1.048247
perc_expiring_sku_sqrt   0.555588   0.570960   0.602379
selling_square_ft_sqrt  30.838581  92.501152  74.418043
Euclidean Distances Between Cluster Centroids:
[[0.         2.2752584  2.65570749 2.26599293]
 [2.2752584  0.         2.91793035 2.07083357]
 [2.65570749 2.91793035 0.         3.1935877 ]
 [2.26599293 2.07083357 3.1935877  0.        ]]
Mean of each variable per cluster (K = 4, not scaled):
label                           0          1          2          3
sold                     0.000000   0.997024   0.316575   1.000000
type_Medium              0.397718   0.691756   0.472879   0.061176
type_Small               0.471092   0.000000   0.289265   0.938789
marca_2_yes              0.989700   0.978074   0.001133   0.965649
unique_skus_brand_sqrt  11.024179  10.957569   3.539862  10.837545
product_cost_sqrt        0.908666   0.908622   1.052253   0.894349
perc_expiring_sku_sqrt   0.524476   0.590855   0.604153   0.581716
selling_square_ft_sqrt  61.067387  93.421199  74.660637  30.372750

Mean of each variable per cluster (K = 3, unscaled):

ks = range(1, 11)
inertias = []

for k in ks:
    model = KMeans(n_clusters=k).fit(df_clust_dummies_scaled)
    inertias.append(model.inertia_)

def kmeans_clustering(df_scaled, k, scaler, numeric_cols):
    df_prod_k = df_scaled.copy()
    model_k = KMeans(n_clusters=k, n_init=10, random_state=100).fit(df_prod_k)
    df_prod_k['label'] = model_k.labels_
    dists = euclidean_distances(model_k.cluster_centers_)

    df_unscaled = df_prod_k.copy()
    cols_to_inverse = [col for col in numeric_cols if col in df_unscaled.columns]
    df_unscaled[cols_to_inverse] = scaler.inverse_transform(df_unscaled[cols_to_inverse]) 
    
    return df_prod_k, dists, df_unscaled, model_k

Euclidean Distances Between Cluster Centroids:
[[0.         2.58797236]
 [2.58797236 0.        ]]
Mean of each variable per cluster (K = 2, not scaled):
label                           0          1
sold                     0.337529   0.618382
type_Medium              0.461763   0.397868
type_Small               0.301254   0.453009
marca_2_yes              0.004563   0.997374
unique_skus_brand_sqrt   3.599177  11.074450
product_cost_sqrt        1.037887   0.907283
perc_expiring_sku_sqrt   0.604951   0.561889
selling_square_ft_sqrt  73.968193  62.661564
Euclidean Distances Between Cluster Centroids:
[[0.         2.04607656 2.94494721]
 [2.04607656 0.         2.65822457]
 [2.94494721 2.65822457 0.        ]]
Mean of each variable per cluster (K = 3, not scaled):
label                           0          1          2
sold                     0.606257   0.632925   0.322645
type_Medium              0.060445   0.708554   0.472006
type_Small               0.939044   0.000000   0.291538
marca_2_yes              0.978063   0.989929   0.000905
unique_skus_brand_sqrt  10.933370  11.030199   3.550519
product_cost_sqrt        0.897487   0.912418   1.048247
perc_expiring_sku_sqrt   0.555588   0.570960   0.602379
selling_square_ft_sqrt  30.838581  92.501152  74.418043
Euclidean Distances Between Cluster Centroids:
[[0.         2.2752584  2.65570749 2.26599293]
 [2.2752584  0.         2.91793035 2.07083357]
 [2.65570749 2.91793035 0.         3.1935877 ]
 [2.26599293 2.07083357 3.1935877  0.        ]]
Mean of each variable per cluster (K = 4, not scaled):
label                           0          1          2          3
sold                     0.000000   0.997024   0.316575   1.000000
type_Medium              0.397718   0.691756   0.472879   0.061176
type_Small               0.471092   0.000000   0.289265   0.938789
marca_2_yes              0.989700   0.978074   0.001133   0.965649
unique_skus_brand_sqrt  11.024179  10.957569   3.539862  10.837545
product_cost_sqrt        0.908666   0.908622   1.052253   0.894349
perc_expiring_sku_sqrt   0.524476   0.590855   0.604153   0.581716
selling_square_ft_sqrt  61.067387  93.421199  74.660637  30.372750

Mean of each variable per cluster (K = 3, unscaled):

def kmeans_clustering(df_scaled, k, scaler, numeric_cols):
    df_prod_k = df_scaled.copy()
    model_k = KMeans(n_clusters=k, n_init=10, random_state=100).fit(df_prod_k)
    df_prod_k['label'] = model_k.labels_
    dists = euclidean_distances(model_k.cluster_centers_)

    df_unscaled = df_prod_k.copy()
    cols_to_inverse = [col for col in numeric_cols if col in df_unscaled.columns]
    df_unscaled[cols_to_inverse] = scaler.inverse_transform(df_unscaled[cols_to_inverse]) 
    
    return df_prod_k, dists, df_unscaled, model_k

def kmeans_clustering(df_scaled, k, scaler, numeric_cols):
    df_prod_k = df_scaled.copy()
    model_k = KMeans(n_clusters=k, n_init=10, random_state=100).fit(df_prod_k)
    df_prod_k['label'] = model_k.labels_
    dists = euclidean_distances(model_k.cluster_centers_)

    df_unscaled = df_prod_k.copy()
    cols_to_inverse = [col for col in numeric_cols if col in df_unscaled.columns]
    df_unscaled[cols_to_inverse] = scaler.inverse_transform(df_unscaled[cols_to_inverse]) 
    
    return df_prod_k, dists, df_unscaled, model_k

k_visualization = [2, 3, 4]
for k in k_visualization:
    df_prod_k, dists, df_unscaled, model_k = kmeans_clustering(df_clust_dummies_scaled, k, scaler, numeric_cols)
    print(f"Euclidean Distances Between Cluster Centroids:\n{dists}")
    print(f"Mean of each variable per cluster (K = {k}, not scaled):")
    print(df_unscaled.groupby(['label']).mean().transpose())

Euclidean Distances Between Cluster Centroids:
[[0.         2.58797236]
 [2.58797236 0.        ]]
Mean of each variable per cluster (K = 2, not scaled):
label                           0          1
sold                     0.337529   0.618382
type_Medium              0.461763   0.397868
type_Small               0.301254   0.453009
marca_2_yes              0.004563   0.997374
unique_skus_brand_sqrt   3.599177  11.074450
product_cost_sqrt        1.037887   0.907283
perc_expiring_sku_sqrt   0.604951   0.561889
selling_square_ft_sqrt  73.968193  62.661564
Euclidean Distances Between Cluster Centroids:
[[0.         2.04607656 2.94494721]
 [2.04607656 0.         2.65822457]
 [2.94494721 2.65822457 0.        ]]
Mean of each variable per cluster (K = 3, not scaled):
label                           0          1          2
sold                     0.606257   0.632925   0.322645
type_Medium              0.060445   0.708554   0.472006
type_Small               0.939044   0.000000   0.291538
marca_2_yes              0.978063   0.989929   0.000905
unique_skus_brand_sqrt  10.933370  11.030199   3.550519
product_cost_sqrt        0.897487   0.912418   1.048247
perc_expiring_sku_sqrt   0.555588   0.570960   0.602379
selling_square_ft_sqrt  30.838581  92.501152  74.418043
Euclidean Distances Between Cluster Centroids:
[[0.         2.2752584  2.65570749 2.26599293]
 [2.2752584  0.         2.91793035 2.07083357]
 [2.65570749 2.91793035 0.         3.1935877 ]
 [2.26599293 2.07083357 3.1935877  0.        ]]
Mean of each variable per cluster (K = 4, not scaled):
label                           0          1          2          3
sold                     0.000000   0.997024   0.316575   1.000000
type_Medium              0.397718   0.691756   0.472879   0.061176
type_Small               0.471092   0.000000   0.289265   0.938789
marca_2_yes              0.989700   0.978074   0.001133   0.965649
unique_skus_brand_sqrt  11.024179  10.957569   3.539862  10.837545
product_cost_sqrt        0.908666   0.908622   1.052253   0.894349
perc_expiring_sku_sqrt   0.524476   0.590855   0.604153   0.581716
selling_square_ft_sqrt  61.067387  93.421199  74.660637  30.372750

def pca_visualization(df_scaled, model_k): 
    df_pca = df_scaled.copy()
    df_pca['cluster'] = df_pca['label']
    df_pca_numeric = df_pca.drop(columns=['label', 'cluster'])
    
    pca = PCA(n_components=2)
    pca_components = pca.fit_transform(df_pca_numeric)
    
    df_pca['PCA1'] = pca_components[:, 0]
    df_pca['PCA2'] = pca_components[:, 1]

    return df_pca

Mean of each variable per cluster (K = 3, unscaled):

def pca_visualization(df_scaled, model_k): 
    df_pca = df_scaled.copy()
    df_pca['cluster'] = df_pca['label']
    df_pca_numeric = df_pca.drop(columns=['label', 'cluster'])
    
    pca = PCA(n_components=2)
    pca_components = pca.fit_transform(df_pca_numeric)
    
    df_pca['PCA1'] = pca_components[:, 0]
    df_pca['PCA2'] = pca_components[:, 1]

    return df_pca

k_visualization = [2, 3, 4]
for k in k_visualization:
    df_prod_k, dists, df_unscaled, model_k = kmeans_clustering(df_clust_dummies_scaled, k, scaler, numeric_cols)
    df_pca = pca_visualization(df_prod_k, model_k)
    
    plt.figure(figsize=(6, 3))
    plt.scatter(df_pca['PCA1'], df_pca['PCA2'], c=df_pca['cluster'], cmap='viridis', s=50)
    plt.title(f'Visualization of Clusters with PCA (K={model_k.n_clusters})')
    plt.xlabel('Principal Component 1')
    plt.ylabel('Principal Component 2')
    plt.colorbar(label='Cluster')
    plt.show()

df_k_2, dists_3, df_unscaled_3, model_k_3 = kmeans_clustering(df_clust_dummies_scaled, 3, scaler, numeric_cols)

def revert_sqrt_columns(df):
    sqrt_cols = [col for col in df.columns if col.endswith('_sqrt')]
    
    for col in sqrt_cols:
        new_col = col.replace('_sqrt', '')
        df[new_col] = df[col] ** 2
        df.drop(col, axis=1, inplace=True)
    
    return df

df_unscaled_3 = revert_sqrt_columns(df_unscaled_3)


print("Mean of each variable per cluster (K = 3, unscaled):")
display(df_unscaled_3.groupby(['label']).mean().transpose())

Mean of each variable per cluster (K = 3, unscaled):

def revert_sqrt_columns(df):
    sqrt_cols = [col for col in df.columns if col.endswith('_sqrt')]
    
    for col in sqrt_cols:
        new_col = col.replace('_sqrt', '')
        df[new_col] = df[col] ** 2
        df.drop(col, axis=1, inplace=True)
    
    return df

df_unscaled_3 = revert_sqrt_columns(df_unscaled_3)


print("Mean of each variable per cluster (K = 3, unscaled):")
display(df_unscaled_3.groupby(['label']).mean().transpose())

Mean of each variable per cluster (K = 3, unscaled):

Attribute	Description
Idstore	Store where the label is applied
type	Store size
selling_square_ft	Store sales area
district	Store location district

Attribute	Description
Idstore	Store where the label is applied
Sku	Internal product identification (Stock Keeping Unit)
Brand	Product brand
Oldpvp	Price before the discount label application
Newpvp (discount)	Price applied during the discount labelling (discount applied)
Labelqty	Number of labels applied (always 1 in the dataset)
Weight	Weight of each SKU
Payment_method	Payment method used in the sale
Margin (%)	Gross margin percentage of the item
Profit (€)	Gross profit in euros
perc_expiring_sku	Proportion of remaining 'shelf life' at the time of the discount label application
expiring_date	Product expiration date
labelling_date	Date of discount labelling applied
sell_date	Date the product was sold
Sold	Boolean variable indicating whether the product was sold (=1) or not (=0) before its expiration

	idstore	sku	brand	oldpvp	new_pvp (discount)	labelqty	weight (g)	Payment_method	Margin (%)	Profit (€)	perc_expiring_sku	expiring_date	labelling_date	sell_date	sold
0	1	1	Marca 1	4.22	2.11 (0.50)	1	260	Card	16	0.3376	0.010050	03/10/2021	2021-10-01	2021-10-02	1.0
1	1	1	Marca 1	3.96	1.98 (0.50)	1	260		16	0.3168	0.005025	02/10/2021	2021-10-01	NaT	0.0
2	1	1	Marca 1	4.74	2.37 (0.50)	1	260	Cash	16	0.3792	0.010050	03/10/2021	2021-10-01	2021-10-03	1.0
3	1	1	Marca 1	4.2	2.1 (0.50)	1	260	Card	16	0.3360	0.010050	03/10/2021	2021-10-01	2021-10-02	1.0
4	1	1	Marca 1	4.62	2.31 (0.50)	1	260	Cash	16	0.3696	0.010050	03/10/2021	2021-10-01	2021-10-03	NaN
5	1	1	Marca 1	4.62	2.31 (0.50)	1	260	Cash	16	0.3696	0.010050	03/10/2021	2021-10-01	2021-10-03	NaN
6	1	1	Marca1	3.42	1.71 (0.50)	1	260	Cash	16	0.2736	0.005025	02/10/2021	2021-10-01	2021-10-02	1.0
7	1	1	Marca 1	3.62	1.81 (0.50)	1	260	Card	16	0.2896	0.005025	02/10/2021	2021-10-01	2021-10-02	1.0
8	1	1	Marca1	4.62	2.31 (0.50)	1	260		16	0.3696	0.005025	02/10/2021	2021-10-01	NaT	0.0
9	1	1	Marca 1	4.38	2.19 (0.50)	1	260	Card	16	0.3504	0.010050	03/10/2021	2021-10-01	2021-10-02	1.0
10	1	1	Marca 1	5.23	NaN	1	260	Card	16	0.4176	0.010050	03/10/2021	2021-10-01	2021-10-03	1.0
11	1	1	Marca 1	5.89	2.94 (0.50)	1	260	Cash	16	0.4704	0.010050	03/10/2021	2021-10-01	2021-10-02	1.0
12	1	1	Marca 1	3.78	1.89 (0.50)	1	260	Cash	16	0.3024	0.010050	03/10/2021	2021-10-01	2021-10-02	1.0
13	1	1	Marca 1	4.76	2.38 (0.50)	1	260		16	0.3808	0.010050	03/10/2021	2021-10-01	NaT	0.0
14	2	2	marca 2	3.19	1.59 (0.50)	1	222	Cash	15	0.2385	0.121212	25/10/2021	2021-10-13	2021-10-14	1.0
15	2	2	marca 2	NaN	1.59 (0.50)	1	222	Cash	15	0.2385	0.121212	25/10/2021	2021-10-13	2021-10-14	1.0
16	2	2	marca2	3.19	1.59 (0.50)	1	222	Card	15	0.2385	0.121212	25/10/2021	2021-10-13	2021-10-15	1.0
17	2	2	marca 2	3.19	1.59 (0.50)	1	222	Cash	15	0.2385	0.121212	25/10/2021	2021-10-13	2021-10-16	1.0

	idstore	sku	labelqty	Margin (%)	Profit (€)	perc_expiring_sku	labelling_date	sell_date	sold	selling_square_ft
count	150054.000000	150054.000000	150054.0	150054.000000	150054.000000	149929.000000	150054	80511	149940.000000	139275.000000
mean	139.234242	163.699422	1.0	15.824950	0.175131	0.385245	2021-10-16 16:41:47.577271808	2021-10-17 18:22:01.846704384	0.536568	5978.197487
min	1.000000	1.000000	1.0	10.000000	0.034000	0.000000	2021-10-01 00:00:00	2021-10-01 00:00:00	0.000000	10.000000
25%	59.000000	99.000000	1.0	13.000000	0.124500	0.200000	2021-10-10 00:00:00	2021-10-11 00:00:00	0.000000	1218.000000
50%	135.000000	160.000000	1.0	17.000000	0.152900	0.333333	2021-10-17 00:00:00	2021-10-18 00:00:00	1.000000	4982.000000
75%	212.000000	236.000000	1.0	19.000000	0.222400	0.666667	2021-10-25 00:00:00	2021-10-25 00:00:00	1.000000	8671.000000
max	332.000000	319.000000	1.0	20.000000	99.900000	2.000000	2021-10-31 00:00:00	2021-11-18 00:00:00	1.000000	25258.000000
std	87.056044	87.247006	0.0	3.303568	0.268439	0.223626	NaN	NaN	0.498663	5533.809602

Variable	Chi-Square	What to do?
type	10 YES	Keep
region	10 YES	Keep
labelling_day_of_week	10 YES	Keep
marca_2	10 YES	Keep

	idstore	type	selling_square_ft	district
0	1	Large	4762.0	Évora
1	2	Large	12358.0	Bragança
2	3	Large	16159.0	Lisboa
3	4	Large	17903.0	Porto
4	5	Large	16179.0	Coimbra
...	...	...	...	...
337	338	Small	1935.0	Leiria
338	339	Small	2680.0	Portalegre
339	340	Small	866.0	Coimbra
340	341	Small	NaN	Castelo Branco
341	342	Small	403.0	Beja

	idstore	sku	brand	oldpvp	new_pvp (discount)	labelqty	weight (g)	Payment_method	Margin (%)	Profit (€)	perc_expiring_sku	expiring_date	labelling_date	sell_date	sold	type	selling_square_ft	district
36	7	2	Marca2	3.19	2.39 (0.25)	1	222		15	0.3585	0.111111	26/10/2021	2021-10-15	NaT	0.0	Large	13618.0	Guarda
39	7	2	Marca 2	3.19	2.39 (0.25)	1	222		15	0.3585	0.111111	26/10/2021	2021-10-15	NaT	0.0	Large	13618.0	Guarda
41	7	2	Marca 2	3.19	2.39 (0.25)	1	222		15	0.3585	0.111111	26/10/2021	2021-10-15	NaT	0.0	Large	13618.0	Guarda
42	7	2	Marca 2	3.19	2.39 (0.25)	1	222		15	0.3585	0.111111	26/10/2021	2021-10-15	NaT	0.0	Large	13618.0	Guarda
43	7	2	Marca 2	3.19	2.39 (0.25)	1	222		15	0.3585	0.111111	26/10/2021	2021-10-15	NaT	0.0	Large	13618.0	Guarda
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
150034	177	318	Marca2	2.49	1,24 (50%)	1	97		11	0.1364	0.006289	19-10-2021	2021-10-18	NaT	0.0	Small	842.0	Porto
150036	177	318	Marca 2	2.49	1,24 (50%)	1	97		11	0.1364	0.006289	19-10-2021	2021-10-18	NaT	0.0	Small	842.0	Porto
150038	177	318	Marca 2	2.49	1,24 (50%)	1	97		11	0.1364	0.006289	19-10-2021	2021-10-18	NaT	0.0	Small	842.0	Porto
150048	193	318	Marca 2	2.49	1.24 (0.50)	1	97	Cash	11	0.1364	0.100629	19-10-2021	2021-10-03	2021-10-09	1.0	Medium	5048.0	Viseu
150051	203	318	Marca2	2.49	1.24 (0.50)	1	97	Cash	11	0.1364	0.113208	19-10-2021	2021-10-01	2021-10-02	1.0	Medium	9370.0	Faro

	brand	oldpvp	new_pvp (discount)	weight (g)	Payment_method	expiring_date	type	district
count	150054	150032.00	150026	150050	150054	150054	150054	150054
unique	328	140.00	396	189	3	84	4	18
top	marca 2	1.19	0.83 (0.30)	121		28/10/2021	Medium	Lisboa
freq	18249	22234.00	18219	9521	69543	4741	62590	14888

	new_pvp (discount)	new_pvp	discount
0	2.11 (0.50)	2.11	(0.50)
1	1.98 (0.50)	1.98	(0.50)
2	2.37 (0.50)	2.37	(0.50)
3	2.1 (0.50)	2.1	(0.50)
4	2.31 (0.50)	2.31	(0.50)
5	2.31 (0.50)	2.31	(0.50)
6	1.71 (0.50)	1.71	(0.50)
7	1.81 (0.50)	1.81	(0.50)
8	2.31 (0.50)	2.31	(0.50)
9	2.19 (0.50)	2.19	(0.50)
10	NaN	nan	NaN

	idstore	sku	brand	oldpvp	labelqty	weight (g)	Payment_method	Margin (%)	Profit (€)	perc_expiring_sku	expiring_date	labelling_date	sell_date	sold	type	selling_square_ft	district	new_pvp	discount
1143	17	8	MARCA2	1.19	1	174.0	Card	15	0.1245	0.2	2021-10-22	2021-10-21	2021-10-20	1	Large	16732.0	Viana do Castelo	0.83	0.3
1144	17	8	MARCA2	1.19	1	174.0	Card	15	0.1245	0.2	2021-10-22	2021-10-21	2021-10-20	1	Large	16732.0	Viana do Castelo	0.83	0.3
1145	17	8	MARCA2	1.19	1	174.0	Cash	15	0.1245	0.2	2021-10-22	2021-10-21	2021-10-20	1	Large	16732.0	Viana do Castelo	0.83	0.3
1146	17	8	MARCA2	1.19	1	174.0	Card	15	0.1245	0.2	2021-10-22	2021-10-21	2021-10-20	1	Large	16732.0	Viana do Castelo	0.83	0.3
39214	17	102	MARCA2	0.99	1	188.0	Card	12	0.0828	0.5	2021-10-22	2021-10-21	2021-10-20	1	Large	16732.0	Viana do Castelo	0.69	0.3
39215	17	102	MARCA2	0.99	1	188.0	Cash	12	0.0828	0.5	2021-10-22	2021-10-21	2021-10-20	1	Large	16732.0	Viana do Castelo	0.69	0.3

	idstore	sku	brand	oldpvp	labelqty	weight (g)	Payment_method	Margin (%)	Profit (€)	perc_expiring_sku	expiring_date	labelling_date	sell_date	sold	type	selling_square_ft	district	new_pvp	discount
452	64	4	MARCA3	500.0	1	134.0	Card	17	0.3315	0.2500	2021-10-30	2021-10-29	2021-10-29	1	Medium	7879.0	Santarém	250.0	0.5
50171	289	108	MARCA20	999.0	1	227.0	No_Payment	20	99.9000	0.1013	2021-10-10	2021-10-02	NaT	0	Small	342.0	Viana do Castelo	499.5	0.5

#	Predictor	Data Type
0	oldpvp	Numerical
1	weight (g)	Numerical
2	Margin (%)	Numerical
3	Profit (€)	Numerical
4	perc_expiring_sku	Numerical
5	sold ----> TARGET	Binary
6	type	Categorical
7	selling_square_ft	Numerical
8	new_pvp	Numerical
9	discount	Numerical
10	labelling_days_until_exp	Numerical
11	region	Categorical
12	marca_2	Categorical
13	skus_per_ft	Numerical
14	skus_per_store	Numerical
15	labelling_day_of_week	Categorical
16	unique_skus_brand	Numerical
17	product_cost	Numerical

Group	Split 1	Split 2	Split 3	Split 4	Split 5	Split 6	Split 7	Split 8	Split 9	Split 10
`oldpvp` vs `new_pvp` vs `product_cost`	1.0	1.0	1.0	1.0	1.0	1.0	1.0	1.0	1.0	1.0
`selling_square_ft` vs `skus_per_ft`	-0.9	-0.9	-0.9	-0.9	-0.9	-0.9	-0.9	-0.9	-0.9	-0.9
`perc_expiring_sku` vs `labelling_days_until_exp`	0.8	0.8	0.8	0.8	0.8	0.8	0.8	0.8	0.8	0.8

Feature	Appearances in the Top 4 (Lasso)	Appearances in the Top 4 (DT)	Average Appearances as True (RFE)	Approach
`weight (g)`	-	-	-	REMOVE
`Margin (%)`	-	-	-	REMOVE
`perc_expiring_sku`	7	-	7	NCERTAINTY
`selling_square_ft`	-	7	-	NCERTAINTY
`skus_per_store`	7	7	7	KEEP
`unique_skus_brand`	7	7	7	KEEP
`product_cost`	7	7	7	KEEP

	F1 Train	F1 Validation	Recall Train	Recall Validation	Precision Train	Precision Validation	Accuracy Train	Accuracy Validation	Running Time
Logistic Regression	0.6236+/-0.0008	0.6233+/-0.0044	0.6351+/-0.0008	0.6348+/-0.0042	0.6387+/-0.0009	0.6384+/-0.0047	0.6351+/-0.0008	0.6348+/-0.0042	0.6674
KNN	0.8123+/-0.0005	0.7109+/-0.0036	0.812+/-0.0005	0.7105+/-0.0036	0.8136+/-0.0004	0.7128+/-0.0035	0.812+/-0.0005	0.7105+/-0.0036	56.1947
DT	0.7217+/-0.0017	0.6816+/-0.0039	0.7222+/-0.0017	0.6821+/-0.0038	0.7218+/-0.0017	0.6816+/-0.0039	0.7222+/-0.0017	0.6821+/-0.0038	0.4728
RFC	0.8031+/-0.0006	0.7218+/-0.0036	0.8038+/-0.0006	0.7228+/-0.0035	0.8043+/-0.0006	0.7224+/-0.0035	0.8038+/-0.0006	0.7228+/-0.0035	32.7012
NB	0.6153+/-0.0008	0.6152+/-0.0048	0.6297+/-0.0008	0.6296+/-0.0046	0.6347+/-0.0009	0.6347+/-0.0054	0.6297+/-0.0008	0.6296+/-0.0046	0.1547
GBC	0.6487+/-0.0014	0.6476+/-0.0046	0.6538+/-0.0012	0.6527+/-0.0046	0.6542+/-0.0012	0.6529+/-0.0049	0.6538+/-0.0012	0.6527+/-0.0046	11.8024
ABC	0.6646+/-0.001	0.6615+/-0.0043	0.6685+/-0.0009	0.6654+/-0.0042	0.6686+/-0.0009	0.6655+/-0.0044	0.6685+/-0.0009	0.6654+/-0.0042	13.4332
SVC	0.6111+/-0.0008	0.6111+/-0.0047	0.628+/-0.0007	0.628+/-0.0044	0.635+/-0.0009	0.635+/-0.0052	0.628+/-0.0007	0.628+/-0.0044	3381.7169
MLP	0.6459+/-0.0036	0.6417+/-0.0034	0.652+/-0.0016	0.6478+/-0.0043	0.6533+/-0.0023	0.649+/-0.0057	0.652+/-0.0016	0.6478+/-0.0043	643.533

	F1 Train	F1 Validation	Recall Train	Recall Validation	Precision Train	Precision Validation	Accuracy Train	Accuracy Validation	Running Time
Logistic Regression	0.6129+/-0.0008	0.6128+/-0.0046	0.628+/-0.0008	0.6279+/-0.0043	0.6334+/-0.0009	0.6333+/-0.0051	0.628+/-0.0008	0.6279+/-0.0043	0.5946
KNN	0.7933+/-0.0006	0.7033+/-0.0033	0.7931+/-0.0006	0.703+/-0.0033	0.7941+/-0.0005	0.7047+/-0.0035	0.7931+/-0.0006	0.703+/-0.0033	15.8553
DT	0.7156+/-0.002	0.6779+/-0.0044	0.716+/-0.0021	0.6783+/-0.0044	0.7156+/-0.0021	0.6778+/-0.0044	0.716+/-0.0021	0.6783+/-0.0044	0.3335
RFC	0.7949+/-0.0004	0.7137+/-0.0033	0.7954+/-0.0004	0.7143+/-0.0033	0.7954+/-0.0004	0.7138+/-0.0033	0.7954+/-0.0004	0.7143+/-0.0033	22.1765
NB	0.6143+/-0.0009	0.614+/-0.0045	0.6295+/-0.0008	0.6292+/-0.0043	0.6352+/-0.0009	0.635+/-0.0051	0.6295+/-0.0008	0.6292+/-0.0043	0.0959
GBC	0.6434+/-0.001	0.6419+/-0.0041	0.6512+/-0.0009	0.6497+/-0.0041	0.6533+/-0.0009	0.6518+/-0.0045	0.6512+/-0.0009	0.6497+/-0.0041	8.1768
ABC	0.6554+/-0.0014	0.6524+/-0.0029	0.661+/-0.0013	0.6581+/-0.0027	0.6622+/-0.0013	0.6592+/-0.0029	0.661+/-0.0013	0.6581+/-0.0027	10.246
SVC	0.6111+/-0.0008	0.6111+/-0.0047	0.628+/-0.0007	0.628+/-0.0044	0.635+/-0.0009	0.635+/-0.0052	0.628+/-0.0007	0.628+/-0.0044	908.9923
MLP	0.6331+/-0.0024	0.6299+/-0.0055	0.6425+/-0.0019	0.6395+/-0.0049	0.6452+/-0.0027	0.6421+/-0.0056	0.6425+/-0.0019	0.6395+/-0.0049	553.7521

Model	F1 Train	F1 Validation	Difference (Train - Val)
RFC	0.8031 ± 0.0007	0.7224 ± 0.0026	0.0807
KNN	0.8120 ± 0.0006	0.7128 ± 0.0031	0.0992
DT	0.7193 ± 0.0031	0.6806 ± 0.0047	0.0387
ABC	0.6644 ± 0.0014	0.6611 ± 0.0030	0.0033
GBC	0.6482 ± 0.0019	0.6472 ± 0.0023	0.0010
LR	0.6236 ± 0.0006	0.6233 ± 0.0023	0.0003

	F1 Train	F1 Validation	Recall Train	Recall Validation	Precision Train	Precision Validation	Accuracy Train	Accuracy Validation	Running Time
Best KNN	0.7469+/-0.0006	0.682+/-0.0041	0.7468+/-0.0006	0.6818+/-0.0041	0.7535+/-0.0007	0.6881+/-0.004	0.7468+/-0.0006	0.6818+/-0.0041	25.3655
Best DT	0.735+/-0.0032	0.6861+/-0.0043	0.7361+/-0.0031	0.6873+/-0.0042	0.7359+/-0.003	0.6866+/-0.0042	0.7361+/-0.0031	0.6873+/-0.0042	0.4926
Best RFC	0.8004+/-0.0007	0.7202+/-0.0032	0.8012+/-0.0008	0.7213+/-0.0031	0.8018+/-0.0008	0.7209+/-0.0032	0.8012+/-0.0008	0.7213+/-0.0031	14.5572

Cluster	Name	sold (%)	Medium Store (%)	Small Store (%)	Marca 2 (%)	Unique SKUs	Cost (€)	Shelf Life (%)	Sales Area (sqft)	Description
0	Best Sellers	63.3%	70.9%	0%	99.0%	122	0.86	37.7%	9094	Medium stores with large sales areas, strong presence of Marca 2 (which has a large variety of SKUs)
1	Compact Efficiency	60.6%	6.0%	93.9%	97.8%	120	0.82	36.2%	1076	Small stores with smaller sales areas and good presence of Marca 2 (which has a large variety of SKUs).
2	Low Performance	32.3%	47.3%	29.0%	0.08%	14	1.12	42.4%	6862	Stores with almost no presence of Marca 2 and brands with low SKU diversity and higher-cost products.

label	0	1	2
sold	0.606257	0.632925	0.322645
type_Medium	0.060445	0.708554	0.472006
type_Small	0.939044	0.000000	0.291538
marca_2_yes	0.978063	0.989929	0.000905
unique_skus_brand	120.664423	122.023158	14.205349
product_cost	0.825345	0.857962	1.123977
perc_expiring_sku	0.361906	0.377179	0.424813
selling_square_ft	1076.125128	9096.905509	6858.341139

0. Defining the Business Goal¶

1. Importing Libraries and Datasets¶

1.1. Import the needed libraries¶

1.2. Datasets¶

1.3. Merging the Datasets¶

1.4. Check for duplicates¶

2.1. Basic Exploration¶

2.2. Statistical Exploration¶

2.2.1. Numerical Variables¶

2.2.2. Categorical Variables¶

3.1. Data Cleaning¶

3.1.1. Missing Values¶

3.1.1.1 Changing Values of the 'sold' Variable¶

3.1.1.2 Splitting the 'new_pvp (discount)' Variable¶

3.1.1.3 Filling missing values of the 'new_pvp' variable¶

3.1.1.4 Filling missing values of the 'oldpvp' variable¶

3.1.1.5 Filling missing values of the 'discount' variable¶

3.1.1.6 Filling missing values of the 'weight (g)' variable¶

3.1.1.7 Filling missing values in the 'perc_expiring_sku' variable¶

3.1.1.8 Filling missing values in the 'Payment_method' variable¶

3.1.1.9 Filling missing values in the 'type' variable¶

3.1.1.10 Filling missing values in the 'selling_square_ft' variable¶

3.1.2. Correction of Formatting¶

3.1.2.1 Correction of the formatting of the 'expiring_date' variable¶

3.1.3. Rounding of Values¶

3.2. Data Transformation¶

3.2.1. Correction of Classifications¶

3.2.2. Correction of Inconsistencies¶

3.2.2.1 Correction of values in the 'sell_date' variable¶

3.2.2.2 Correction of values in the 'perc_expiring_sku' variable¶

3.2.2.3 Removal of values from the 'oldpvp' and 'new_pvp' variables¶

3.2.2.4 Change of inconsistent values in the 'discount' variables¶

3.2.3. Creation of New Variables¶

3.2.3.1 Conversion of datetime type variables¶

3.2.3.3 Creation of the 'region' variable¶

3.2.3.4 Creation of the 'marca_2' flag variable¶

3.2.3.5 Creation of the variables 'skus_per_ft','skus_per_store','labelling_day_of_week','unique_skus_brand','skus_per_brand','product_cost'¶

3.2.4. Elimination of variables¶

3.2.5. Visual Exploration¶

3.2.5.1 Numeric Variables¶

3.2.5.2 Categorical Variables¶

3.2.6. Outliers¶

3.2.6.1 Analysis of skewness and kurtosis¶

3.2.6.2 Visual Analysis of Outliers¶

3.2.7. Scalling¶

4.1. Data Preparation: Train-Test Split¶

4.2. Feature Selection¶

4.2.1. Categorical Variables - Chi-Square (Filter Method)¶

4.2.2. Numerical Variables - Variance (Filter Method)¶

4.2.3. Correlation Analysis - Spearman (Filter Method)¶

4.2.4. Selection of Correlated Variables - Decision Trees (Embedded Method)¶

4.2.5. Variable Selection - Multi-Methods¶

4.2.5.1 Recursive Feature Elimination (RFE) (Wrapper Method)¶

4.2.5.2 Lasso Regression (Embedded Method)¶

4.2.5.3 Methods Comparison¶

4.2.5.4 Feature Selection Analysis (Lasso, DT, and RFE)¶

5.1. Model Selection¶

5.2. Data Preparation - Train - Test Split¶

5.3. Algorithm Comparison and Selection¶

5.3.1. Training and Validation - Dataset all_data¶

5.3.2. Training and Validation - Dataset keep_data¶

5.3.3. Conclusions¶

5.4.1. K-Nearest Neighbor¶

5.4.1. Decision Trees¶

5.4.2. Random Forest¶

5.4.4. Conclusions¶

7.1. Dummy Variables¶

7.2. Power Transformation¶

7.3. Normalization¶

7.4. Exploration of the Ideal K¶

7.4.1. Elbow Method¶

7.4.2. K-means Clustering¶

7.4.3. PCA for Clustering Visualization¶

7.5. K Choice: K=3¶

7.5.1 Revert SQRT¶

7.6. Cluster Analysis¶